Biggest Window Between Visits - Practice Coding Problems

Biggest Window Between Visits - Problem

Database Medium

Biggest Window Between Visits

You're given a table UserVisits containing user visit logs for a retail store. Your task is to find the largest gap between consecutive visits for each user.

Table Structure:
UserVisits
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id | int |
| visit_date | date |
+-------------+------+

The Challenge:
For each user, calculate the maximum number of days between any two consecutive visits. If a user's last visit was before today (2021-1-1), include the gap from their last visit to today.

Key Points:
• The table may contain duplicate rows
• Consider today's date as '2021-1-1'
• Return results ordered by user_id
• Calculate the biggest window between consecutive visits

Input & Output

example_1.sql — Basic Example

$ Input: UserVisits: | 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 |

› Output: | user_id | biggest_window | |---------|----------------| | 1 | 39 | | 2 | 65 | | 3 | 51 |

💡 Note: User 1: gaps are 39 days (Oct 20 to Nov 28), 5 days (Nov 28 to Dec 3), and 29 days (Dec 3 to today). Maximum is 39. User 2: gaps are 65 days (Oct 5 to Dec 9) and 23 days (Dec 9 to today). Maximum is 65. User 3: only one visit, so gap is 51 days (Nov 11 to today).

example_2.sql — With Duplicates

$ Input: UserVisits: | user_id | visit_date | |---------|------------| | 1 | 2020-01-01 | | 1 | 2020-01-01 | | 1 | 2020-06-01 | | 2 | 2020-12-31 |

› Output: | user_id | biggest_window | |---------|----------------| | 1 | 214 | | 2 | 1 |

💡 Note: User 1 has duplicate visits on 2020-01-01 (removed), then gaps of 152 days (Jan 1 to Jun 1) and 214 days (Jun 1 to today). Maximum is 214. User 2 has only one visit with 1 day gap to today.

example_3.sql — Single Visit Users

$ Input: UserVisits: | user_id | visit_date | |---------|------------| | 1 | 2020-12-31 | | 2 | 2020-01-01 | | 3 | 2020-06-15 |

› Output: | user_id | biggest_window | |---------|----------------| | 1 | 1 | | 2 | 366 | | 3 | 200 |

💡 Note: All users have only one visit each. The biggest window is the gap from their single visit to today (2021-01-01). User 1: 1 day, User 2: 366 days (leap year), User 3: 200 days.

Visualization

Tap to expand

Understanding the Visualization

Collect Visit Data

Gather all customer visit dates, removing any duplicate entries

Sort Chronologically

Order visits by date for each customer to identify consecutive visits

Calculate Gaps

Measure days between each pair of consecutive visits

Include Today Gap

Add the gap from last visit to today's date (2021-01-01)

Find Maximum

Select the largest gap for each customer

Key Takeaway

🎯 Key Insight: Window functions like LAG() and LEAD() allow us to access adjacent rows efficiently, making consecutive difference calculations optimal with O(n log n) complexity instead of O(n²) nested queries.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting for window function, then single pass calculation

⚡ Linearithmic

Space Complexity

O(n)

Storage for window function intermediate results

⚡ Linearithmic Space

Constraints

1 ≤ Number of rows ≤ 10⁵
1 ≤ user_id ≤ 1000
visit_date is between '2020-01-01' and '2020-12-31'
Today's date is fixed as '2021-01-01'
Table may contain duplicate rows

Asked in

G Google 35 a Amazon 42 f Meta 28 ⊞ Microsoft 31

The optimal solution uses window functions (LAG/LEAD) to efficiently calculate gaps between consecutive visits. Key steps: 1) Remove duplicates with DISTINCT, 2) Use LAG() to get previous visit dates, 3) Calculate date differences with DATEDIFF(), 4) Handle the gap from last visit to today, 5) Find MAX() gap per user.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function Approach (Optimal)	O(n log n)	O(n)	Use LAG() window function to efficiently calculate consecutive visit gaps
Nested Query Approach	O(n²)	O(n)	Use nested queries to compare all visit pairs for each user

Window Function Approach (Optimal) — Algorithm Steps

Remove duplicate visits using DISTINCT
Use LAG() window function to get previous visit date for each user
Calculate difference between current and previous visit dates
Handle the gap from last visit to today's date
Find maximum gap for each user

Visualization

Tap to expand

Step-by-Step Walkthrough

Remove Duplicates

Get unique visits per user

Apply Window Functions

Use LAG() and LEAD() to get previous and next visit dates

Calculate Gaps

Compute date differences for each consecutive pair

Find Maximum

Group by user and select maximum gap

Code -

solution.c — C

// Optimal SQL solution using window functions
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = 
    "WITH visit_gaps AS ( "
    "    SELECT "
    "        user_id, "
    "        visit_date, "
    "        LAG(visit_date) OVER (PARTITION BY user_id ORDER BY visit_date) as prev_date, "
    "        LEAD(visit_date) OVER (PARTITION BY user_id ORDER BY visit_date) as next_date "
    "    FROM ( "
    "        SELECT DISTINCT user_id, visit_date "
    "        FROM UserVisits "
    "    ) unique_visits "
    "), "
    "calculated_gaps AS ( "
    "    SELECT "
    "        user_id, "
    "        CASE "
    "            WHEN next_date IS NULL THEN "
    "                DATEDIFF('2021-01-01', visit_date) "
    "            ELSE "
    "                DATEDIFF(next_date, visit_date) "
    "        END as gap_days "
    "    FROM visit_gaps "
    ") "
    "SELECT "
    "    user_id, "
    "    MAX(gap_days) as biggest_window "
    "FROM calculated_gaps "
    "GROUP BY user_id "
    "ORDER BY user_id;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting for window function, then single pass calculation

⚡ Linearithmic

Space Complexity

O(n)

Storage for window function intermediate results

⚡ Linearithmic Space

Constraints

1 ≤ Number of rows ≤ 10⁵
1 ≤ user_id ≤ 1000
visit_date is between '2020-01-01' and '2020-12-31'
Today's date is fixed as '2021-01-01'
Table may contain duplicate rows

28.4K Views

Medium Frequency

~18 min Avg. Time

856 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Function Approach (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler