Weekly Signup Cohort Retention
Group users into weekly cohorts by signup week (weeks start Monday). For signups up to 2026-03-31, report the share of each cohort who logged in during week 1 (days 7-13 after signup) and during week 4 (days 28-34 after signup). Day 0 is the signup date.
Expected output
cohort_week (Monday, YYYY-MM-DD), users, week1_pct, week4_pct (rounded to 1 decimal). Sorted by cohort_week.
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)
- Average Order Value by Country (easy)
- Quarterly Cancellation Rate (medium)
- Repeat Purchase Rate (medium)
- Days from Signup to First Order (medium)