sql
Longest monthly activity streak
SQL & Data Modeling · Window Functions · medium · Original Uplift question · PostgreSQL · 18 min
For each user, return the length of their longest streak of consecutive calendar months with at least one activity. Multiple activities in a month count once. Return user_id and longest_streak, ordered by user_id.
Fixture
user_activity
user_id, activity_date
CREATE TABLE user_activity (user_id INT, activity_date DATE); INSERT INTO user_activity VALUES (1,'2025-01-03'),(1,'2025-01-20'),(1,'2025-02-04'),(1,'2025-03-15'),(1,'2025-05-01'), (2,'2025-01-01'),(2,'2025-03-01'),(2,'2025-04-01'),(2,'2025-05-01'), (3,'2025-07-07');
Evaluation criteria
- Deduplicates user-months before streak logic
- Identifies consecutive months with a gaps-and-islands method
- Returns one row per user
- Produces streaks 3, 3, and 1 for users 1, 2, and 3
Detailed grading feedback appears after you submit.
Source
Original Uplift question based on a common interview pattern.
Your answer
You get one graded attempt here without an account. Create a free account for unlimited attempts, saved progress and community write-ups.
Answers stay hidden until you ask. Reveal the full model write-up when you are ready to compare.