The Revenue That Walked

Loopwire's retention team is measured on one number: how much monthly billing the company has actually lost. MRR — monthly recurring revenue — is what a contract bills every month, and a contract keeps that figure on file after it stops, as a record of what it used to bring in.

Adding up every stopped contract's MRR would be the obvious answer and it would be too big, because most stopped contracts here did not lose Loopwire a penny. Loopwire never edits a contract when a customer moves tier: it stops the old one and writes a new one the same day, on the same account. Money only really walked out of the door when the account behind the stopped contract has nothing running at all any more.

subscriptions — one row per contract. account_id identifies the customer, and the same customer may own several of these rows. plan is the tier that contract bought. mrr is what it bills every month, in dollars. start_date is the day it began. A contract that has stopped carries the day it stopped in end_date; one that is still running has nothing there, shown as — below and stored as NULL.

The table already exists in the database — there is nothing to create or load.

Task: Write a query that returns one row and one column, churned_mrr, holding the combined monthly billing of the stopped contracts whose account has no contract still running.

Example output