The Account Leaderboard

Loopwire's customer-success team works a tiered service model, and the tier a customer sits in is decided by a leaderboard of what they pay. MRR — monthly recurring revenue — is what a live contract bills each month, and a customer paying for two things at once is worth both together. The team's one firm rule is that customers paying the same amount must be given the same position: whoever is drawing up the list does not get to break a tie by preference.

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.

accounts — one row per customer company. industry is the sector that company works in; signup_date is the day it created its Loopwire account.

Both tables already exist in the database — there is nothing to create or load.

Task: Write a query that returns one row per company with at least one running contract, with columns company_name, account_mrr (that company's combined monthly billing across its running contracts) and mrr_rank — its position on the leaderboard, 1 being the highest-paying. Companies on equal billing share a position, and the positions immediately after a shared one are skipped, so the column can read 1, 1, 3. Rows come back by position, and companies sharing a position are listed alphabetically by company name.

Example output

Shape only — these companies and figures are invented. Two are level at the top, so they share position 1 and position 2 goes unused:

(...2 more rows not shown)