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;
Breadth first search can be used to find the shortest path between two nodes in a directed graph.
Solution Implementation
Which of the following is the prefix sum of array [1, 2, 3, 4, 5]
?
Recommended Readings
Patterns The Shortest Path Algorithm for Coding Interviews The goal of AlgoMonster is to help you get a job in the shortest amount of time possible in a data driven way We compiled datasets of tech interview problems and broke them down by patterns This way we can determine the
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 Monster 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.