Level 7 · Boss: the 2025 revenue bridge

What a revenue bridge is

A bridge (or waterfall) walks from one number to another in visible steps. A revenue bridge starts at gross sales and subtracts each leak in order:

gross sales     2,410,185.52
− discounts        47,953.28
= net sales     2,362,232.24
− refunds         140,869.57
= net revenue   2,221,362.67

Each line is one query, and the lines must add up. If they don’t, a filter is different between two of them, which is exactly the kind of error a bridge exposes and a single headline hides.

One base, three cuts

All three tables in this boss come from the same lines CTE: valid lines of booked 2025 orders. The first table is the bridge for the year. The second is the same arithmetic grouped by month. The third groups by sales channel and adds two ratio KPIs: AOV and refund rate. Starting every cut from one base guarantees the months and the channels add back to the year.

Joining refunds safely

returns has at most one row per order line, so a LEFT JOIN on order_item_id adds a refund amount to returned lines and NULL to the rest, without duplicating any sales. SUM skips the NULLs; COALESCE(..., 0) turns a group with no returns at all into 0 instead of NULL, so net revenue stays a number.

Reading the months and the channels

November and December are the biggest months, as in every retail year. December’s refunds look low ($8,573.75 against November’s $21,306.68) only because December orders have had a few days to come back; the bridge should say so. Across channels, web orders are the largest on average and the least returned, the app brings the most orders, and the marketplace is smallest on both counts.

Common mistakes

• Inner join to returns. Only returned lines survive.
• COUNT(*) as orders. It counts lines and divides AOV by about 2.5.
• Different filters per table. If the monthly table includes pending orders and the bridge does not, the board sees two different years.

Interview note

Take-home tasks often ask for “revenue by month and channel”. Lead with your definition, show a bridge, then the cuts. Reviewers check whether your totals reconcile before they read anything else.