+-------------+------+
|Column Name |Type|+-------------+------+
| user_id | int || visit_date | date |+-------------+------+
This table does not have a primarykey, it might contain duplicate rows.
This tablecontains logs of the dates that users visited a certain retailer.
Assume today’s date is '2021-1-1'.
Write a solution that will, for each user_id, find out the largest window of days between each visit and the one right after it (or today if you are considering the last visit).
Return the result table ordered by user_id.
The query result format is in the following example.
Input:
UserVisits table:
+---------+------------+
| 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|+---------+------------+
Output:
+---------+---------------+
| user_id | biggest_window|+---------+---------------+
|1|39||2|65||3|51|+---------+---------------+
Explanation:
For the firstuser, the windows in question arebetween dates:
-2020-10-20and2020-11-28with a total of39 days.
-2020-11-28and2020-12-3with a total of5 days.
-2020-12-3and2021-1-1with a total of29 days.
Making the biggest window the one with39 days.
For the seconduser, the windows in question arebetween dates:
-2020-10-5and2020-12-9with a total of65 days.
-2020-12-9and2021-1-1with a total of23 days.
Making the biggest window the one with65 days.
For the third user, the only window in question isbetween dates 2020-11-11and2021-1-1with a total of51 days.
For each user, sort their visits, add a virtual visit for today (2021-01-01), and compute the difference in days between each consecutive visit. The biggest window is the maximum of these differences.
SELECT user_id,
MAX(DATEDIFF(next_date, visit_date)) AS biggest_window
FROM (
SELECT user_id, visit_date,
LEAD(visit_date, 1, '2021-01-01') OVER (PARTITION BY user_id ORDERBY visit_date) AS next_date
FROM UserVisits
) t
GROUPBY user_id
ORDERBY user_id;