How Long Do Churned Deals Last?

Loopwire is building its first churn model — churn being a customer stopping their subscription — and the modeller's first request is the plainest possible training input: for every contract that has already ended, how many days it lasted from the day it began to the day it stopped. Contracts still running have no answer yet and must stay out of the file, since a half-lived contract entered as a completed one would teach the model that customers leave sooner than they do.

subscriptions — one row per contract. account_id points at a row in accounts. plan is the tier the customer bought. mrr is what that contract 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 two columns, id and lifetime_days, one row for every contract that has stopped, lifetime_days being the whole number of days between its start date and its end date. Longest-lived first; equal lifetimes are listed with the smaller id first.

Example output

Shape only — these contracts and spans are invented: