marketing_touches(user_id, channel, touched_at)

Return every run of five or more consecutive calendar days on which a user was touched, keeping only the runs that involved at least two distinct channels. Several touches on one day count once towards the length.

Return user_id, streak_start, streak_end, days and distinct_channels, ordered by user then start date.

The channel count has to be measured over the run, not over the user, so the islands have to be found first and the channels counted back against them.

Tables

marketing_touches

user_id  channel  touched_at
-------  -------  -------------------
10       email    2026-03-01 09:00:00
10       email    2026-03-02 09:00:00
10       push     2026-03-02 18:00:00
10       email    2026-03-03 09:00:00
10       email    2026-03-04 09:00:00
10       email    2026-03-05 09:00:00
10       email    2026-03-06 09:00:00
11       email    2026-03-01 09:00:00
... 9 more row(s)

Expected result

user_id  streak_start  streak_end  days  distinct_channels
-------  ------------  ----------  ----  -----------------
10       2026-03-01    2026-03-06  6     2