Skip to main content
Talentd

Biggest Window Between Visits - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

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.

Approach Overview

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.

Solution

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.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Self Join to Find Next VisitO(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

Video Solution

LeetCode Medium 1709 Interview SQL Question with Detailed Explanation • Everyday Data Science • 3,220 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is Biggest Window Between Visits easy or hard?
Biggest Window Between Visits is considered a medium difficulty database problem. The challenge comes from recognizing the need to compare each visit with the next visit and correctly handling the final gap using SQL window functions.
Biggest Window Between Visits Python/Java solution
The problem is primarily a SQL database problem rather than a typical Python or Java algorithm task. On platforms like LeetCode, the expected solution is a MySQL query using the LEAD window function and DATEDIFF calculation.
How to solve Biggest Window Between Visits in O(n)?
Pure O(n) is uncommon in SQL because rows must usually be sorted by visit_date within each user. The closest practical approach uses LEAD with a partitioned sort, resulting in O(n log n) complexity while keeping the query simple and efficient.
What is the best approach for Biggest Window Between Visits?
The best approach uses the SQL window function LEAD. Partition rows by user_id and order by visit_date, then compute the difference between the current visit and the next visit. Replace NULL with '2021-01-01' for the final visit and take the MAX gap per user.
Is Biggest Window Between Visits asked at Google/Amazon/Meta?
This type of SQL problem appears frequently in data engineering and analytics interviews at companies like Amazon, Meta, and Google. Variants involving time gaps, session windows, or consecutive events are common interview patterns.
What data structure is used in Biggest Window Between Visits?
The solution relies on SQL window functions rather than traditional data structures. Internally, the database engine sorts rows and processes partitions to compute the next-row value using the LEAD function.
What is the time complexity of Biggest Window Between Visits?
The query typically runs in O(n log n) time because the database engine sorts visits within each user partition before applying the window function. Space complexity is O(n) for intermediate query processing.

Ready to solve this problem?

Practice Biggest Window Between Visits with our built-in code editor and test cases.

Practice on FleetCode
Previous

Users With Two Purchases Within Seven Days