logins(user_id, login_date)

Return every run of consecutive login days for each user: where it starts, where it ends, and how many days long it is. A single isolated day is a run of one.

Duplicate (user_id, login_date) rows count once.

Return user_id, island_start, island_end and days, ordered by user then start date.

Subtracting a row number from the date gives every day in a run the same value, which is then just a GROUP BY.

Tables

logins

user_id  login_date
-------  ----------
10       2026-03-01
10       2026-03-02
10       2026-03-02
10       2026-03-03
10       2026-03-10
11       2026-01-30
11       2026-01-31
11       2026-02-01

Expected result

user_id  island_start  island_end  days
-------  ------------  ----------  ----
10       2026-03-01    2026-03-03  3
10       2026-03-10    2026-03-10  1
11       2026-01-30    2026-02-01  3