The Whale Watch

Loopwire has three customer-success managers and far more customers than that, so dedicated attention goes to the largest contracts by monthly billing. The manager building this week's rota wants the three biggest contracts on file, with enough on each line to look the customer up: which account it belongs to, which tier it is, and what it bills. Several contracts bill the same amount as each other, so which of them lands in third place is a real question, and she needs it settled the same way every week rather than however the database feels.

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 exactly three rows, with columns account_id, plan and mrr — the three contracts with the highest monthly billing. Highest first; two contracts billing the same amount are listed with the smaller account_id first. Cancelled contracts are eligible too.

Example output

Shape only — these accounts and figures are invented; the tier names are the real ones: