Lesson 7 of 11 · 28 min
Retention, churn and MRR movements
By the end of this lesson you can compute paying customers and MRR on any date, calculate logo churn for a period, and break the change in MRR into new, expansion, contraction, churned and reactivation movements.
Mizan Cloud sells monthly and annual subscriptions. Its subscriptions table has one row per continuous period on one plan: a trial row, then a paid row, a new row on each upgrade or downgrade (the old row becomes 'superseded'), and a 'cancelled' row when the customer leaves. mrr is the row’s monthly recurring revenue after discounts; ended_at is exclusive (the row is no longer active on that day) and NULL while it runs.
Two rules from the KPI handbook apply to every metric in this lesson:
- Paying means a non-trial subscription row active on the date:
started_at <= d AND (ended_at IS NULL OR ended_at > d). Status'past_due'still counts: the customer has not left yet. - Test accounts (
accounts.is_test_account) are excluded everywhere.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons