activity(user_id, activity_date)

Return each user's longest run of consecutive active days. Where two runs tie on length, take the one that starts earliest. Duplicate rows for the same user and date count once.

Return user_id, streak_start, streak_end and streak_days, ordered by user_id. Every user with any activity gets exactly one row.

Finding the islands is half of it; the other half is picking one per user, which MAX(length) cannot do because it loses the dates that go with it.

Tables

activity

user_id  activity_date
-------  -------------
10       2026-01-01
10       2026-01-02
10       2026-01-03
10       2026-02-10
10       2026-02-11
10       2026-02-12
11       2026-03-01
11       2026-03-02
... 5 more row(s)

Expected result

user_id  streak_start  streak_end  streak_days
-------  ------------  ----------  -----------
10       2026-01-01    2026-01-03  3
11       2026-03-01    2026-03-03  3
12       2026-04-04    2026-04-04  1