Revenue Concentration

Loopwire is considering a price change and the CFO wants to know how exposed the company is before anyone touches a number. MRR — monthly recurring revenue — is what a live contract bills each month. If one tier carries most of it, a mistake on that tier is a company-level event; if the revenue is spread evenly, the same mistake is a bad quarter. Raw dollar totals per tier do not answer that on their own — the CFO wants each tier's slice of the whole, as a percentage.

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 one row per plan that has at least one running contract, with columns plan and mrr_pct — that plan's share of the total monthly billing of all running contracts, as a percentage rounded to 1 decimal place. Stopped contracts are excluded from both the slice and the whole. Largest share first; equal shares are listed alphabetically by plan name.

Example output

Shape only — the tier names are the real ones, the figures are invented. A whole percentage comes back as 32, not 32.0: