# $1709. 访问日期之间最大的空档期
# https://leetcode-cn.com/problems/biggest-window-between-visits/

# SQL架构
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');

# Write your MySQL query statement below
select
    tmp.user_id,
    max(datediff(tmp.next_day, tmp.visit_date)) as biggest_window
from
    (
        select
            user_id,
            visit_date,
            lead(visit_date, 1, '2021-1-1') over (
                partition by user_id
                order by
                    visit_date
            ) as next_day
        from
            UserVisits
    ) tmp
group by
    tmp.user_id
order by
    tmp.user_id;

# clean-up
drop table UserVisits;