🔥 Seven Days. No Breaks. No Excuses.
Inside Duolingo's Growth Engineering bunker, the streak feature is sacred. It is the single most powerful retention mechanism the app has ever shipped. Users who maintain streaks churn at 3x lower rates. Streaks are money.
Your product manager has come to you with an urgent request before the weekly all-hands: 'Show me every user who has logged in for at least 7 consecutive days at any point in their history.'
This sounds simple. It is not. This is one of the most infamous SQL interview questions in the industry — asked at Meta, Google, LinkedIn, and Uber. It requires a technique that most people have never seen: the date minus row number grouping trick.
The logic: if you subtract the ROW_NUMBER (within each user, ordered by date) from the login date, consecutive dates all produce the same result. That result becomes your group ID. You count rows per group. If any group has 7 or more rows — that user has a 7-day streak.
Your task: Find all users who have logged in for 7 or more consecutive days. Return user_id, name, and their longest streak length.
users Table:
| Column Name | Type |
|---|---|
| user_id | integer |
| name | text |
logins Table:
| Column Name | Type |
|---|---|
| login_id | integer |
| user_id | integer |
| login_date | text (YYYY-MM-DD, one row per day per user, no duplicates) |
Expected Outcome (Illustrative Sample):
| user_id | name | longest_streak |
|---|---|---|
| 1 | Alice Chen | 9 |
This is the query that separates the analysts from the engineers. Write it.