A user is active in a calendar month if they have at least one transaction in it. Find each user's longest run of consecutive active calendar months (December -> January of the next year is consecutive; the same month in different years is not). Return user_id, longest_month_streak and streak_start_month ('YYYY-MM', earliest run on ties) for users whose longest streak is at least 3. Order by longest_month_streak DESC, then user_id.
| Column | Type |
|---|---|
| txn_id | INTEGER |
| user_id | INTEGER |
| txn_date | DATE |
| amount | REAL |
| txn_id | user_id | txn_date | amount |
|---|---|---|---|
| 1 | 1 | 2026-01-03 | 120 |
| 2 | 1 | 2026-01-25 | 80 |
| 3 | 1 | 2026-02-14 | 300 |
| 4 | 1 | 2026-03-09 | 45 |
| 5 | 2 | 2026-01-10 | 99 |
| 6 | 2 | 2026-03-11 | 150 |
| 7 | 2 | 2026-04-02 | 60 |
| 8 | 3 | 2026-02-01 | 500 |
| user_id | longest_month_streak | streak_start_month |
|---|---|---|
| 3 | 4 | 2026-02 |
| 1 | 3 | 2026-01 |
Solve it in the live editor on this page — run on sample data, then submit against hidden tests.
{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.
Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.
{{ cur.statement }}
No submissions yet. Press Evaluate to submit your query.
{{ solutionSql }}
{{ out.error }}
You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.