Longest daily login streak per user

The product team measures habit formation by the longest run of consecutive calendar days on which a user logged in. Users can log in several times a day, and days can be skipped.

From user_logins, return for each user user_id, longest_streak_days, streak_start and streak_end for their longest streak. If a user has several streaks of the same length, return the earliest one. Order by user_id.

1. Step 1: Remove duplicate logins first with `SELECT DISTINCT user_id, login_date`; two logins on the same day must count once.
2. Step 2: Gaps-and-islands trick: within a user, subtract `ROW_NUMBER()` (as days) from the date. Consecutive days produce the same result, so each unbroken streak gets its own `streak_key`.
3. Step 3: Group by `user_id, streak_key` to get each streak's start, end and length.
4. Step 4: Rank each user's streaks by length descending, then earliest start, and keep rank 1.

WITH daily AS (
    SELECT DISTINCT user_id, login_date
    FROM user_logins
),
grouped AS (
    SELECT user_id,
           login_date,
           login_date - ROW_NUMBER() OVER (
               PARTITION BY user_id ORDER BY login_date
           )::int AS streak_key
    FROM daily
),
streaks AS (
    SELECT user_id,
           MIN(login_date) AS streak_start,
           MAX(login_date) AS streak_end,
           COUNT(*)        AS streak_days
    FROM grouped
    GROUP BY user_id, streak_key
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY streak_days DESC, streak_start
           ) AS rn
    FROM streaks
)
SELECT user_id,
       streak_days AS longest_streak_days,
       streak_start,
       streak_end
FROM ranked
WHERE rn = 1
ORDER BY user_id;