Largest Gap Between User Visits

MEDIUM20 pts

A retail analytics team records each customer check-in date in a visit log table. They want to measure the longest inactivity span per user so retention programs can prioritize users with large gaps between visits.

Table: user_visits

user_id: integer user identifier
visit_date: date of a recorded visit (YYYY-MM-DD)

Data characteristics:

The table has no primary key.
Duplicate rows can exist.
A user may have multiple rows on the same date.

Reference date:

Treat 2021-01-01 as the reporting anchor date.

Task: For each user_id, compute the maximum number of days between:

one visit and that user's next visit by date
the last recorded visit and 2021-01-01

Output requirements:

Return exactly two columns: user_id, biggest_window
Output one row per user who appears in user_visits
Sort by user_id ascending

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

user_visits:
| 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":1,"biggest_window":39},
  {"user_id":2,"biggest_window":65},
  {"user_id":3,"biggest_window":51}
]

Explanation

For user 1, sorted visits are 2020-10-20, 2020-11-28, 2020-12-03. Gaps are 39, 5, and 29 days to the anchor date; max is 39.

Example

Input

user_visits:
| user_id | visit_date  |
|---------|-------------|
| 10      | 2020-12-25  |
| 10      | 2020-12-25  |
| 10      | 2020-12-31  |
| 20      | 2020-01-01  |
| 20      | 2020-06-01  |

Output

[
  {"user_id":10,"biggest_window":6},
  {"user_id":20,"biggest_window":152}
]

Explanation

Duplicate same-day visits are valid. For user 10, the largest gap is from 2020-12-25 to 2020-12-31 (6 days). For user 20, largest gap is 152 days from 2020-01-01 to 2020-06-01.

Example

Input

user_visits:
[]

Output

[]

Explanation

If there are no visit records, there are no users to report.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in user_visits <= 200000
1 <= user_id <= 1000000000
visit_date is a valid date in YYYY-MM-DD format
visit_date <= 2021-01-01
The table may contain duplicate rows
For each user, compute gaps after sorting by visit_date ascending
For each user's final visit, compare against 2021-01-01
Return exactly two columns: user_id, biggest_window
Sort by user_id in ascending order
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns user_id and biggest_window

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

user_visits =

[{"user_id":1,"visit_date":"2020-11-28"},{"user_id":1,"visit_date":"2020-10-20"},{"user_id":1,"visit_date":"2020-12-03"},{"user_id":2,"visit_date":"2020-10-05"},{"user_id":2,"visit_date":"2020-12-09"},{"user_id":3,"visit_date":"2020-11-11"}]

Largest Gap Between User Visits

Hints

Largest Gap Between User Visits

Hints