Group consecutive dates into streaks

WITH numbered AS (
  SELECT user_id,
         activity_date,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER BY activity_date
         ) AS rn
  FROM daily_activity
), grouped AS (
  SELECT user_id,
         activity_date,
         activity_date - rn * INTERVAL '1 day' AS grp_key
  FROM numbered
)
SELECT user_id,
       MIN(activity_date) AS streak_start,
       MAX(activity_date) AS streak_end,
       COUNT(*) AS streak_days
FROM grouped
GROUP BY user_id, grp_key;