SQL · Intermediate · 35 min
Users active on seven consecutive days
Find streaks without counting repeated events as extra days.
Task
The activity table records user_id and activity_day. A user may have several events on one day. Return every user with a streak of at least seven consecutive calendar days. A streak can cross a month or year boundary.
Requirements
- Return one column named user_id, ordered ascending.
- Deduplicate user/day pairs before finding streaks.
- A missing calendar day breaks a streak. Seven events on one day do not qualify.
- Dates are valid ISO YYYY-MM-DD strings. Use SQLite syntax.
SQL windowsDatesGaps and islands
Loading exercise…