Table: UserVisits
+-------------+------+ | Column Name | Type | +-------------+------+ | user_id | int | | visit_date | date | +-------------+------+ This table does not have a primary key, it might contain duplicate rows. This table contains 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.
Example 1:
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 first user, the windows in question are between dates:
- 2020-10-20 and 2020-11-28 with a total of 39 days.
- 2020-11-28 and 2020-12-3 with a total of 5 days.
- 2020-12-3 and 2021-1-1 with a total of 29 days.
Making the biggest window the one with 39 days.
For the second user, the windows in question are between dates:
- 2020-10-5 and 2020-12-9 with a total of 65 days.
- 2020-12-9 and 2021-1-1 with a total of 23 days.
Making the biggest window the one with 65 days.
For the third user, the only window in question is between dates 2020-11-11 and 2021-1-1 with a total of 51 days.
Problem Overview: The table stores user_id and visit_date. For each user, compute the largest number of days between two consecutive visits. The final gap also considers the days between the user’s last recorded visit and 2021-01-01. Return the maximum gap per user.
Approach 1: Self Join on Next Visit (O(n log n) time, O(n) space)
Start by ordering visits per user by visit_date. For each visit, find the next visit belonging to the same user using a self join or correlated subquery that searches for the minimum later date. Compute the difference with DATEDIFF(next_visit, visit_date). If no later visit exists, calculate the difference between 2021-01-01 and the current visit. Finally, group by user_id and take the maximum gap. This works in any SQL dialect but requires repeated lookups or joins, which makes it slower when the visit table is large.
Approach 2: Window Function with LEAD (O(n log n) time, O(n) space)
The clean solution uses SQL window functions from SQL. Partition rows by user_id and order them by visit_date. Use the LEAD(visit_date) function to access the next visit for the same user. This removes the need for manual joins. Calculate the gap using DATEDIFF(LEAD(visit_date), visit_date). If LEAD returns NULL (meaning the current row is the last visit), replace it with 2021-01-01 using IFNULL or COALESCE. The result is the number of days until the next visit or the end date.
After computing the gap for every row, aggregate with MAX() grouped by user_id. Window functions avoid repeated joins and make the query easier to read and maintain. This pattern appears frequently in database interview problems that involve "next row" comparisons. Understanding LEAD and LAG is essential for solving many database problems efficiently, especially those involving sequential records or time-series gaps. See more patterns in window functions.
Recommended for interviews: The window function approach is the expected solution. A self join demonstrates understanding of relational queries, but LEAD shows stronger SQL skills and produces a shorter, more efficient query.
We can use the window function LEAD to obtain the date of the next visit for each user (if the date of the next visit does not exist, it is considered as 2021-1-1), and then use the DATEDIFF function to calculate the number of days between two visits. Finally, we can take the maximum value of the number of days between visits for each user.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self Join to Find Next Visit | O(n log n) | O(n) | When window functions are unavailable or using older SQL dialects |
| Window Function (LEAD) | O(n log n) | O(n) | Best general solution using modern SQL window functions |
LeetCode Medium 1709 Interview SQL Question with Detailed Explanation • Everyday Data Science • 3,101 views views
Watch 2 more video solutions →Practice Biggest Window Between Visits with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor