Largest Gap Between User Visits

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:

1. one visit and that user's next visit by date
2. 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)

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  |

[
  {"user_id":1,"biggest_window":39},
  {"user_id":2,"biggest_window":65},
  {"user_id":3,"biggest_window":51}
]

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  |

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

user_visits:
[]

[]