SQL Cohort Drill
Six challenges against a real SQLite database, built in your browser from a product that has something wrong with it. Write the query, check it against a reference answer, and then read why the number you just computed is easier to misread than it looks. Retention that decays, a cohort that was bought rather than earned, a revenue figure indexed to a month that was only half a month.
Cohort triangles, rolling windows, revenue retention and stickiness. Nothing is sent anywhere and nothing is stored.
Challenges
Schema
One row per signup. 2025 only.
| user_id | INTEGER | Primary key |
| signup_date | TEXT | 'YYYY-MM-DD'. SQLite has no date type — dates are text, which is why strftime does the work |
| country | TEXT | UK, US, DE, IN or BR |
| plan | TEXT | free, pro or team |
One row per action. No row means no activity that day.
| user_id | INTEGER | Joins to users.user_id |
| event_date | TEXT | 'YYYY-MM-DD' |
| event_type | TEXT | login, create, share or purchase |
| revenue | REAL | Zero for everything except purchase |
1. Signups by month
Count signups per calendar month, oldest first. This is the denominator every retention figure on this page divides by, so it is worth looking at before anything else. One of the nine months should make you stop.
cohort_month, signups. Column names are not graded.