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;