Level 2 · Which orders count?

Statuses tell you what happened

Every order at Harbor & Pine ends up in one of four statuses. In 2025:

SELECT status, COUNT(*) FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
GROUP BY status;
-- completed 4,295 · refunded 114 · cancelled 275 · pending 99

• Completed: paid and delivered. Some lines may still come back as returns.
• Refunded: paid, delivered, then returned in full. Every line is in returns.
• Cancelled: stopped before payment. No money changed hands.
• Pending: not settled yet. Almost all of these were placed in the last ten days of December.

Demand, bookings and revenue

Three different questions use three different order sets.

Demand (how many orders did customers try to place?) counts everything: 4,783. It is useful for traffic and checkout health, because a spike in cancellations is a problem worth seeing.

Bookings (what did customers pay for?) counts completed and refunded orders: 4,409 orders and $2,362,232.24 of net sales. This is the order count behind AOV, orders per customer and net sales across this track.

Net revenue (what did the store keep?) starts from bookings and subtracts refunds. Keeping refunded orders in bookings and subtracting the refund separately makes returns a visible line in the report instead of orders that quietly vanished.

Why not just completed?

Filtering to status = 'completed' looks clean and gives $2,301,690.14. But it treats the 114 refunded orders as if they never happened, while completed orders with partial returns still count in full. Returns end up half-hidden, and your return rate looks better than it is. The booked definition treats every return the same way.

Common mistakes

• Counting pending orders as sales. They are mostly from the last days of the year; count them once they complete, in the year they were placed.
• Different filters on numerator and denominator. AOV = net sales / orders only makes sense if both use the same order set.
• Silent definitions. Put the status filter in the footnote of every revenue number.

Interview note

When an interviewer asks “how would you calculate revenue from an orders table?”, ask back which statuses exist. Saying “I’d exclude cancelled and pending, keep refunded and subtract the refunds” shows you think about definitions before code, which is the point of the question.