SQL实战训练之，力扣：1709. 访问日期之间最大的空档期

目录

        一、力扣原题链接

        二、题目描述

        三、建表语句

        四、题目分析        

        五、SQL解答

        六、最终答案

        七、验证

        八、知识点

一、力扣原题链接

1709. 访问日期之间最大的空档期

二、题目描述

用户访问量表： UserVisits

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| visit_date  | date |
+-------------+------+
该表没有主键，它可能有重复的行
该表包含用户访问某特定零售商的日期日志。

假设今天的日期是 '2021-1-1' 。

编写解决方案，对于每个 user_id ，求出每次访问及其下一个访问（若该次访问是最后一次，则为今天）之间最大的空档期天数 window 。

返回结果表，按用户编号 user_id 排序。

结果格式如下示例所示：

示例 1：

输入：
UserVisits 用户访问量表：
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1       | 2020-11-28 |
| 1       | 2020-10-20 |
| 1       | 2020-12-3  |
| 2       | 2020-10-5  |
| 2       | 2020-12-9  |
| 3       | 2020-11-11 |
+---------+------------+
输出：
+---------+---------------+
| user_id | biggest_window|
+---------+---------------+
| 1       | 39            |
| 2       | 65            |
| 3       | 51            |
+---------+---------------+
解释：
对于第一个用户，问题中的空档期在以下日期之间：
    - 2020-10-20 至 2020-11-28 ，共计 39 天。
    - 2020-11-28 至 2020-12-3 ，共计 5 天。
    - 2020-12-3 至 2021-1-1 ，共计 29 天。
由此得出，最大的空档期为 39 天。
对于第二个用户，问题中的空档期在以下日期之间：
    - 2020-10-5 至 2020-12-9 ，共计 65 天。
    - 2020-12-9 至 2021-1-1 ，共计 23 天。
由此得出，最大的空档期为 65 天。
对于第三个用户，问题中的唯一空档期在 2020-11-11 至 2021-1-1 之间，共计 51 天。

三、建表语句

drop table if exists UserVisits;
Create table If Not Exists UserVisits(user_id int, visit_date date);
Truncate table UserVisits;
insert into UserVisits (user_id, visit_date) values ('1', '2020-11-28');
insert into UserVisits (user_id, visit_date) values ('1', '2020-10-20');
insert into UserVisits (user_id, visit_date) values ('1', '2020-12-3');
insert into UserVisits (user_id, visit_date) values ('2', '2020-10-5');
insert into UserVisits (user_id, visit_date) values ('2', '2020-12-9');
insert into UserVisits (user_id, visit_date) values ('3', '2020-11-11');

四、题目分析

第一步：排名（根据用户id分组，访问日期倒序排序）

第二步：条件判断（排名1的结束日期为2021-01-01，其他则为上一个访问日期）

第三步：计算日期差（结束日期 - 访问日期）空档期

第四步：根据用户id分组找最大值

五、SQL解答

-- 1、排名
select
    distinct
    *,
    dense_rank() over (partition by user_id order by visit_date desc) as dr
from uservisits
;

-- 2、根据排名的条件获得结束日期
with t1 as (
    select
        distinct
        *,
        dense_rank() over (partition by user_id order by visit_date desc) as dr
    from uservisits
)
select
    user_id,
    visit_date,
    case
        when dr = 1 then '2021-01-01'
        else lag(visit_date) over (partition by user_id order by visit_date desc)
    end as end_date
from t1
;

-- 3、结束日期 - 开始日期 等于 日期差
with t1 as (
    select
        distinct
        *,
        dense_rank() over (partition by user_id order by visit_date desc) as dr
    from uservisits
),t2 as (
    select
        user_id,
        visit_date,
        case
            when dr = 1 then '2021-01-01'
            else lag(visit_date) over (partition by user_id order by visit_date desc)
        end as end_date
    from t1
)
select
    user_id,visit_date,end_date,
    datediff(end_date,visit_date) as diff
from t2
;

-- 4、根据用户ID分组，取最大的差值
with t1 as (
    select
        distinct
        *,
        dense_rank() over (partition by user_id order by visit_date desc) as dr
    from uservisits
),t2 as (
    select
        user_id,
        visit_date,
        case
            when dr = 1 then '2021-01-01'
            else lag(visit_date) over (partition by user_id order by visit_date desc)
        end as end_date
    from t1
),t3 as (
select
    user_id,visit_date,end_date,
    datediff(end_date,visit_date) as diff
from t2
)
select
    user_id,
    max(diff) as biggest_window
from t3
group by user_id
order by user_id
;

六、最终答案

with t1 as (
    select
        distinct
        *,
        -- 1、排名
        dense_rank() over (partition by user_id order by visit_date desc) as dr
    from uservisits
),t2 as (
    select
        user_id,
        visit_date,
        -- 2、条件判断得出结束日期
        case
            when dr = 1 then '2021-01-01'
            -- 上一行的值
            else lag(visit_date) over (partition by user_id order by visit_date desc)
        end as end_date
    from t1
),t3 as (
select
    user_id,visit_date,end_date,
    -- 3、计算日期差（空窗期）
    datediff(end_date,visit_date) as diff
from t2
)
select
    user_id,
    -- 4、分组求最大
    max(diff) as biggest_window
from t3
group by user_id
order by user_id
;

七、验证

八、知识点

• distinct 去重
• dense_rank 排名
• case..when 条件判断
• lag 上一行的值
• max 最大值