Biggest Window Between Visits - Problem

Table: UserVisits

Column NameType
user_idint
visit_datedate

This table does not have a primary key and might contain duplicate rows. Each row contains the date that a user visited a certain retailer.

Assume today's date is '2021-01-01'.

Write a solution that will, for each user_id, find 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.

Table Schema

UserVisits
Column Name Type Description
user_id int ID of the user
visit_date date Date when user visited the retailer
Primary Key: none
Note: No primary key, may contain duplicate rows. Represents visitor logs.

Input & Output

Example 1 — Multiple Users with Different Visit Patterns
Input Table:
user_id visit_date
1 2020-11-28
1 2020-10-20
1 2020-12-03
2 2020-10-05
2 2020-12-09
2 2020-12-07
Output:
user_id biggest_window
1 39
2 65
💡 Note:

For user 1: visits on 2020-10-20, 2020-11-28, 2020-12-03. Gaps are 39 days (Oct 20 to Nov 28), 5 days (Nov 28 to Dec 3), and 29 days (Dec 3 to today 2021-01-01). Maximum gap is 39 days.

For user 2: visits on 2020-10-05, 2020-12-07, 2020-12-09. Gaps are 63 days, 2 days, and 23 days to today. Maximum gap is 65 days (Oct 5 to Dec 7).
Example 2 — Single Visit User
Input Table:
user_id visit_date
1 2020-10-05
Output:
user_id biggest_window
1 88
💡 Note:

User 1 has only one visit on 2020-10-05. The only gap is from this visit to today (2021-01-01), which is 88 days.

Constraints

  • 1 ≤ user_id ≤ 100
  • visit_date is a valid date before '2021-01-01'
  • Table may contain duplicate rows

Visualization

Tap to expand
Finding Biggest Window Between VisitsUserVisitsuser_idvisit_date12020-10-2012020-11-2822020-12-09LEAD() + MAX()GROUP BY user_idResultuser_idbiggest_window139223Calculate gaps between consecutive visits and to today (2021-01-01)Return maximum gap per user
Understanding the Visualization
1
Input
User visit dates table
2
LEAD Function
Get next visit date per user
3
Output
Maximum gap per user
Key Takeaway
🎯 Key Insight: LEAD() window function efficiently calculates gaps between consecutive rows within partitions

Related Problems

Asked in
Facebook 28 Amazon 15
23.5K Views
Medium Frequency
~18 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen