Problem Description:

We are given a table called UserVisits which contains logs of the dates that users visited a specific retailer. The table has two columns user_id and visit_date. We have to write an SQL query to find out the largest window of days between each visit and the one right after it (or today if we are considering the last visit) for each user_id. We return the result table ordered by user_id.

Example:

Given UserVisits table:

The result table should be:

Solution Approach:

To solve this problem, we first need to create a temporary table with visit dates sorted for each user. Then, calculate the difference in days between each visit and the visit right after it. In the end, find out the largest difference for each user_id and return the result as the biggest_window.

Example:

Let's take the same UserVisits table as before:

1. Create a temporary table with user_id and their sorted visit dates.

2. Calculate the difference between each visit and the visit right after it.

3. Find the largest difference for each user_id and return the result.

Solution:

SQL

WITH SortedVisits AS (
  SELECT user_id, visit_date, 
         LEAD(visit_date) OVER (PARTITION BY user_id ORDER BY visit_date) next_visit_date
  FROM UserVisits
)

SELECT user_id, MAX(DATEDIFF(IFNULL(next_visit_date, '2021-01-01'), visit_date)) biggest_window
FROM SortedVisits
GROUP BY user_id
ORDER BY user_id;