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
Biggest Window Between Visits INPUT user_id visit_date 1 2020-11-28 1 2020-10-20 1 2020-12-03 2 2020-10-05 2 2020-12-09 3 2020-11-11 Timeline View (User 1) 10/20 11/28 12/03 01/01 (today) 39d 5d 29d Reference Date: 2021-01-01 ALGORITHM STEPS 1 Order Visits Sort by user_id, visit_date 2 Add Reference Date Append 2021-01-01 per user 3 Calculate Gaps Use LAG() or LEAD() 4 Find Maximum MAX(gap) per user WITH ordered AS ( SELECT user_id, visit_date, LEAD (visit_date) OVER (PARTITION BY user_id ORDER BY visit_date) as next ) SELECT MAX(gap)... FINAL RESULT user_id max_gap 1 39 2 65 3 51 Gap Breakdown (User 2) 10/05 --> 12/09: 65 days 12/09 --> 01/01: 23 days Maximum: 65 days OK - Results ordered by user_id ASC with max window days Key Insight: Use LEAD() or LAG() window functions to compare consecutive visits after ordering by date. Include reference date (2021-01-01) as a virtual "last visit" using UNION ALL before calculating gaps. DATEDIFF() computes days between dates, then GROUP BY user_id with MAX() finds the biggest window. TutorialsPoint - Biggest Window Between Visits | Optimal Solution

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