Churned Accounts and Lost MRR
An account has churned if its most recent subscription (latest started_at) ended on or before 2026-04-30. List churned accounts with their churn date and the MRR of that last subscription.
Expected output
account_id, company, plan, churn_date, lost_mrr. Sorted by churn_date, then account_id.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: B2B SaaS product events
A project-management SaaS: company accounts, their users, product event logs (Jan to Apr 2026) and subscription history.
accounts
One row per customer company.
| account_id | INTEGER | primary key |
| company | TEXT | |
| industry | TEXT | |
| country | TEXT | |
| created_at | TEXT | YYYY-MM-DD |
users
One row per user. Every user belongs to one account.
| user_id | INTEGER | primary key |
| account_id | INTEGER | |
| signup_date | TEXT | YYYY-MM-DD, same day as the user's signup event |
| acquisition_channel | TEXT | organic, paid_search, referral or partner |
| country | TEXT |
events
Product event log. Data ends on 2026-04-30.
| event_id | INTEGER | primary key, increases with event_time |
| user_id | INTEGER | |
| event_name | TEXT | signup, login, view_dashboard, export_report, create_project, invite_teammate, upgrade_click |
| event_time | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
subscriptions
Paid subscription periods per account. An account with no rows is on the free plan.
| subscription_id | INTEGER | primary key |
| account_id | INTEGER | |
| plan | TEXT | team or business |
| mrr | REAL | monthly recurring revenue in USD |
| started_at | TEXT | YYYY-MM-DD, inclusive |
| ended_at | TEXT | YYYY-MM-DD, exclusive; NULL while active. May be in the future for a scheduled cancellation. |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Monthly Revenue Trend (easy)
- Quarterly Cancellation Rate (medium)
- Top 2 Products in Each Category (medium)
- Days from Signup to First Order (medium)
- Running Revenue Total for 2025 (medium)