Level 4 · What the coupons cost

Rate is not cost

Every coupon at Harbor & Pine has a fixed rate: BFCM25 takes 25% off, WELCOME10 10%. The rate tells you how generous a code is. It does not tell you what it cost, because cost is rate × the sales it was used on. A code is expensive when it is generous and popular.

quantity * unit_price * discount_pct / 100   -- discount dollars on one line

Summed per code, that is the dollar cost. Marketing compares codes by rate; finance compares them by dollars. This level gives both their number.

Share of the total with a window

To say “BFCM25 is 30% of all coupon cost” each row needs the total across every code. A window function over the grouped result gives it without a second query:

SUM(SUM(discount_expr)) OVER ()

The inner SUM is the group’s discount; the outer SUM ... OVER () adds those group sums across all rows. Divide the first by the second, multiply by 100.0, round once.

What 2025 shows

BFCM25 cost $14,367.33 on 112 orders over one long weekend. WELCOME10 cost $10,126.31 on 198 orders. SPRING15, HOLIDAY20 and SUMMER15 each cost between $7,200 and $8,900. In all, coupons took $47,953.28 off 2025 gross sales, about 2% of $2.41M.

FREESHIP shows 79 orders and $0 discount. Its cost is real, but it is the shipping fee the store did not charge, so it lives in orders.shipping_fee. A coupon report that only reads discount_pct misses it; say so in the footnote.

Cost is half the question

The other half is what the coupon bought. WELCOME10 brings in new customers, whose later orders carry no discount. BFCM25 may mostly discount orders people would have placed anyway. Cost per code is where that conversation starts, and chapter 2’s customer lifetime value is where it continues.

Common mistakes

• Ranking by rate. The 25% code is not automatically the costliest.
• Forgetting free shipping. A discount column only holds price discounts.
• Joining returns first. Discounts are a sales-side number; keep refunds out of this query.

Interview note

“How would you measure whether a promotion was worth it?” Start with its cost in dollars, then the incremental orders and the customers it acquired. Cost alone never decides it.