Level 2 · Check and compare coupon orders in SQL

What you are checking, and why

The coupon lives in two places: the code on orders.coupon_code and the discount on every line in order_items.discount_pct. Every number in this case is built from the second, so first prove it agrees with the first. One grouped query does it:

SELECT COALESCE(o.coupon_code, 'none') AS coupon,
       MIN(i.discount_pct) AS min_discount,
       MAX(i.discount_pct) AS max_discount
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
GROUP BY coupon;

If a code’s minimum and maximum are equal, all its lines carry the same discount. Three other things to look for in the same result: a code written two ways (welcome10 next to WELCOME10), orders without a code that still carry a discount, and how many orders have no code at all. COALESCE matters here: without it the NULL group is easy to filter away by accident.

What each coupon cost

Step 2 rolls the lines up to one row per order first, in a CTE, because a coupon is an order-level choice: the average order value must divide by orders, not by lines. From there the cost sheet is plain aggregation:

• revenue: what customers paid, after the discount;
• aov: revenue per order;
• discount_cost: value before discount minus value after, the money given away;
• revenue_share_pct: each code’s slice of all net revenue, divided by a scalar subquery (SELECT SUM(net) FROM order_totals) so the denominator is the same total for every row.

Like for like: the same days

Every coupon order averages less than an order without a code. That sounds damning, but the seasonal codes run in particular weeks, and Black Friday baskets are smaller across the board. The fair comparison puts each coupon next to the orders placed on the same days without a code.

The second CTE, coupon_days, lists the days each seasonal code was used. Joining it back to order_totals on the day gives every order placed on those days, once per coupon. Conditional aggregation then splits them:

AVG(CASE WHEN t.coupon = d.coupon THEN t.net END) AS coupon_aov,
AVG(CASE WHEN t.coupon = 'none'   THEN t.net END) AS no_coupon_aov

WELCOME10 and FREESHIP are left out of this step because they run every day: for them, “the same days” means the whole year.

Reading the result

coupon	coupon_basket	coupon_aov	no_coupon_aov
BFCM25	536.54	402.41	483.06
HOLIDAY20	482.84	386.27	479.56
SPRING15	634.59	539.40	574.96
SUMMER15	671.75	570.98	658.07

The basket before the discount is about the same as, or a little above, a normal order on those days. So the codes did not make people buy much more; they mostly paid less for a similar basket.

Pitfalls

• A coupon order counted once per line inflates orders; count in the order-level CTE.
• Filtering quantity > 0 matters: three coupon lines have a zero or negative quantity.
• FREESHIP’s cost is shipping, which is not in these totals. Mention it rather than reporting $0.