Longest Login Streak
Hard · Window Functions
This is a "gaps and islands" problem. A neat trick: number each user's distinct login days with ROW_NUMBER() and subtract that number (as days) from the date. Consecutive days give the same result, so that value identifies a streak (see the example).
A user logged in on a day if they have any login on that calendar day (several logins on one day count once). A streak is a run of consecutive calendar days with logins. For every user (including users who never logged in), return name, longest_streak (the length in days of their longest streak, 0 if they never logged in) and streak_start (the first day of that streak as a date; if several streaks share the longest length, the earliest one; NULL if they never logged in).
Sort by longest_streak descending, then name.