Skip to content
RED SAGSkills
hard20 pts · first answer countssqlwindow-functions

Longest login streak

A user logged in on days 1, 2, 3, 5, 6 and 9 (one row per day). The query groups days by d minus ROW_NUMBER. What is the largest streak length it finds?

WITH g AS (
  SELECT d, d - ROW_NUMBER() OVER (ORDER BY d) AS grp
  FROM logins
)
SELECT MAX(cnt) FROM (
  SELECT grp, COUNT(*) AS cnt FROM g GROUP BY grp
) s;
Choose one answer