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_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 |
The result table should be:
user_id | biggest_window |
---|---|
1 | 39 |
2 | 65 |
3 | 51 |
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:
- Create a temporary table with user_id and their sorted visit dates.
user_id | visit_date | sorted_visit_date |
---|---|---|
1 | 2020-11-28 | 2020-10-20 |
1 | 2020-10-20 | 2020-11-28 |
1 | 2020-12-3 | 2020-12-3 |
2 | 2020-10-5 | 2020-10-5 |
2 | 2020-12-9 | 2020-12-9 |
3 | 2020-11-11 | 2020-11-11 |
- Calculate the difference between each visit and the visit right after it.
user_id | visit_date | sorted_visit_date | difference |
---|---|---|---|
1 | 2020-11-28 | 2020-10-20 | 39 |
1 | 2020-10-20 | 2020-11-28 | 5 |
1 | 2020-12-3 | 2020-12-3 | 29 |
2 | 2020-10-5 | 2020-10-5 | 65 |
2 | 2020-12-9 | 2020-12-9 | 23 |
3 | 2020-11-11 | 2020-11-11 | 51 |
- Find the largest difference for each user_id and return the result.
user_id | biggest_window |
---|---|
1 | 39 |
2 | 65 |
3 | 51 |
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
Top Patterns to Conquer the Technical Coding Interview Should the written word bore you fear not A delightful video alternative awaits iframe width 560 height 315 src https www youtube com embed LW8Io6IPYHw title YouTube video player frameborder 0 allow accelerometer autoplay clipboard write encrypted media gyroscope picture in picture
Recursion Recursion is one of the most important concepts in computer science Simply speaking recursion is the process of a function calling itself Using a real life analogy imagine a scenario where you invite your friends to lunch https algomonster s3 us east 2 amazonaws com recursion jpg You first
Runtime Overview When learning about algorithms and data structures you'll frequently encounter the term time complexity This concept is fundamental in computer science and offers insights into how long an algorithm takes to complete given a certain input size What is Time Complexity Time complexity represents the amount of time
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.