task Gaps & islands · hard
Longest login streak
For each shopper in app_logins, return shopper_id and longest_streak: the most consecutive calendar days on which they logged in.
what to return
- Columns
shopper_idlongest_streakin this order- Rows
- 6 rows · any order
- Watch for
- Several logins on the same day count as one day.
- Streaks may cross a month boundary.
- Skill
- Find runs of consecutive days. Refresher: CROSS JOIN & a complete date grid →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result6 rows · any order
| shopper_id | longest_streak |
|---|---|
| 1 | 5 |
| 2 | 3 |
| 3 | 1 |
first 3 of 6 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
app_logins2 columns · 35 rows
| shopper_idinteger | login_datetext |
|---|---|
| 1 | 2025-03-01 |
| 1 | 2025-03-02 |
| 1 | 2025-03-03 |
| 1 | 2025-03-04 |
| 1 | 2025-03-07 |
showing 5 of 35 rows
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.