Skip to content

1709. Biggest Window Between Visits 👍

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
WITH
  UserToWindow AS (
    SELECT
      user_id,
      DATEDIFF(
        LEAD(visit_date, 1, '2021-01-01') OVER(
          PARTITION BY user_id
          ORDER BY visit_date
        ),
        visit_date
      ) AS `window`
    FROM userVisits
  )
SELECT
  user_id,
  MAX(`window`) AS biggest_window
FROM UserToWindow
GROUP BY user_id;