SaaS Graded Drills (Easy → Hard)
These drills build subscription-metrics fluency in increasing difficulty. All dates are fixed: the dataset runs 2023-06 through 2024-05, and the latest observed month is 2024-05. Never use CURRENT_DATE.
Scoring suggestion:
- Easy drills: pass all
- Medium drills: pass at least one
- Hard drill: pass it before moving to the capstone
Easy 1: Current MRR by Plan #
For the latest observed month (2024-05), report total MRR per plan. Return plan and total_mrr, largest plans first.
Easy 2: ARR by Account Segment #
ARR is a run-rate: MRR x 12. For the latest observed month (2024-05), report MRR and ARR per account segment. Segment lives on the saas_accounts dimension, so this is your first dimension join: the subscription fact has no segment column.
Medium 1: Month-over-Month MRR Growth #
For every month in the window, report total MRR, the previous month's total (LAG), and the month-over-month growth rate (total_mrr - prev_mrr) / prev_mrr rounded to 4 decimals with a NULLIF guard. The first month (2023-06) has no prior month, so its prev_mrr and mom_growth are NULL, not zero.
Medium 2: Logo Churn #
Logo churn counts lost accounts, not lost dollars. Definition (be precise; graders are): an account is a churned logo if its subscription rows stop before the latest observed month (2024-05) and never resume after the stop. Equivalently: its MAX(month_start) is earlier than 2024-05-01.
The trap is Quill & Parcel (account 7): it has a two-month gap (2024-01 and 2024-02) but resumes in 2024-03, so its last row is in 2024-05: a reactivation, not a churned logo. Taking the maximum month per account handles this automatically; checking for "any missing month" does not.
Return account_id, account_name, and last_active_month, earliest churn first.
Hard 1: MRR Bridge for 2024-03 #
Decompose the March 2024 MRR movement into a one-month bridge. Definitions, comparing 2024-03 against 2024-02 at account grain:
- starting_mrr: total MRR in 2024-02
- new_mrr: MRR of accounts paying in 2024-03 with no row in 2024-02 (this lumps the reactivated account 7 in with genuinely new accounts; the capstone splits them apart)
- expansion_mrr: for accounts in both months, the sum of increases
- contraction_mrr: for accounts in both months, the sum of decreases (as a positive number)
- churned_mrr: 2024-02 MRR of accounts with no 2024-03 row
- ending_mrr: total MRR in 2024-03
The identity starting + new + expansion - contraction - churned = ending must hold; a grader checks it. A FULL OUTER JOIN between the two month slices sees all three populations (only-new, only-churned, both) in one pass.
Every drill here is a grain decision in disguise: plan grain, segment grain, month grain, account grain, account-pair grain. Name the grain out loud before writing the first JOIN and the bridge identity will hold itself.
Next Step #
Continue with: