CTEs and Window Functions in SQL: Subscription Churn Metrics

<p>Rozkhata, a Pune start-up that sells GST billing software to kirana stores, pharmacies and other small shops on monthly plans, has a board meeting coming, and an investor who wants three numbers: MRR, churn and net revenue retention. The billing dashboard and the finance team already disagree about the first one. Meera Joshi, Rozkhata's first data analyst, decides to build all three from the billing data in SQL, so they can be rerun every month instead of argued about.</p><p>Across five lessons you build them with her, on a copy of the billing database in an SQL lab that runs in your browser: a CTE that defines a paying month once (paid, recurring, before GST, no demo shops), month-on-month growth with LAG, a financial-year running total with PARTITION BY, logo churn with LEAD and the NULLs it hides, reactivations after a gap, the five MRR movements (new, expansion, contraction, churn, reactivation), an MRR bridge that adds up, and net and gross revenue retention with a RANK by segment. Every lesson you write the query yourself first, then hand the same job to your AI (ChatGPT, Claude, Copilot or Gemini) and learn exactly where it goes wrong: filtering before the window, ignoring gaps, dropping the NULLs, UNION instead of UNION ALL. In the capstone you build net and gross revenue retention yourself, then your AI writes the whole board pack from one brief, and you prove every number against your own before it goes to the board.</p>

Tools: SQL (MySQL), Common table expressions (WITH), Window functions (LAG, LEAD, RANK), PARTITION BY and running totals, Date arithmetic with DATE_FORMAT and DATE_ADD, MRR, churn and NRR metrics, AI assistants for SQL