1709. Biggest Window Between Visits


Problem Description:

We are given a table called UserVisits which contains logs of the dates that users visited a specific retailer. The table has two columns user_id and visit_date. We have to write an SQL query to find out the largest window of days between each visit and the one right after it (or today if we are considering the last visit) for each user_id. We return the result table ordered by user_id.

Example:

Given UserVisits table:

user_idvisit_date
12020-11-28
12020-10-20
12020-12-3
22020-10-5
22020-12-9
32020-11-11

The result table should be:

user_idbiggest_window
139
265
351

Solution Approach:

To solve this problem, we first need to create a temporary table with visit dates sorted for each user. Then, calculate the difference in days between each visit and the visit right after it. In the end, find out the largest difference for each user_id and return the result as the biggest_window.

Example:

Let's take the same UserVisits table as before:

  1. Create a temporary table with user_id and their sorted visit dates.
user_idvisit_datesorted_visit_date
12020-11-282020-10-20
12020-10-202020-11-28
12020-12-32020-12-3
22020-10-52020-10-5
22020-12-92020-12-9
32020-11-112020-11-11
  1. Calculate the difference between each visit and the visit right after it.
user_idvisit_datesorted_visit_datedifference
12020-11-282020-10-2039
12020-10-202020-11-285
12020-12-32020-12-329
22020-10-52020-10-565
22020-12-92020-12-923
32020-11-112020-11-1151
  1. Find the largest difference for each user_id and return the result.
user_idbiggest_window
139
265
351

Solution:

SQL

1WITH SortedVisits AS (
2  SELECT user_id, visit_date, 
3         LEAD(visit_date) OVER (PARTITION BY user_id ORDER BY visit_date) next_visit_date
4  FROM UserVisits
5)
6
7SELECT user_id, MAX(DATEDIFF(IFNULL(next_visit_date, '2021-01-01'), visit_date)) biggest_window
8FROM SortedVisits
9GROUP BY user_id
10ORDER BY user_id;

Recommended Readings


Got a question? Ask the Teaching Assistant anything you don't understand.

Still not clear? Ask in the Forum,  Discord or Submit the part you don't understand to our editors.

←
↑TA đŸ‘šâ€đŸ«