Revenue and orders

“What was revenue last year?” sounds like the easiest question an analyst gets. It has at least four honest answers, and the one you give depends on which orders you count, whether discounts and refunds come off, and whether shipping is in. Most revenue arguments in a meeting are two people using different definitions of the same word.

This track uses one set of house definitions, the same in every chapter, so every number you compute can be compared with every other:

• Valid line: order_items.quantity > 0. The 24 lines with zero or negative quantity are data-entry errors and are left out.
• Line gross = quantity * unit_price (the price paid on the line). Line discount = line gross × discount_pct / 100. Line net = gross − discount.
• Booked order: status IN ('completed', 'refunded'), the orders a customer actually paid for. Cancelled orders were never paid; pending ones have not settled yet. A refunded order was paid and then sent back in full.
• Gross sales = Σ line gross on booked orders. Net sales = Σ line net on booked orders. Refunds = Σ returns.refund_amount (returns join order_items on order_item_id). Net revenue = net sales − refunds. Shipping fees are not revenue here.
• Year windows: order_date >= '2025-01-01' AND order_date < '2026-01-01'. Refunds count in the year of the order they belong to.

Code levels alternate between SQL and pandas; the tag on each level says which. The boss is a 2025 revenue bridge your Head of Finance can put in front of the board.