Drill 7 of 12 in Food delivery questions
0

Interview Question Bank · NULLIF · COALESCE · CASE groups

7. Revenue with and without promo codes

MediumFood deliveryQuestion #235

Concept: NULLIF · COALESCE · CASE groupsshow

Missing values often come in more than one disguise: NULL and an empty string '' both mean 'nothing here', but only NULL passes IS NULL. NULLIF(x, '') turns the empty string into NULL so one test catches both. In arithmetic a NULL spreads (10 - NULL is NULL), so wrap optional amounts in COALESCE(x, 0) before subtracting.

Example (different data):

SELECT CASE WHEN NULLIF(referrer, '') IS NULL THEN 'direct' ELSE 'referred' END AS source,
  COUNT(*) AS signups,
  SUM(plan_price - COALESCE(credit, 0)) AS billed
FROM signups
GROUP BY 1;

Your task

Brightbowl's growth team ran WELCOME10 and LUNCH5 promotions and wants to compare promo orders with full-price ones. An older app version saved 'no code' as an empty string instead of NULL.

Using delivered orders only, put each order in promo_group 'promo' (a non-empty promo_code) or 'no promo' (promo_code NULL or ''). Return promo_group, orders, avg_subtotal (ROUND(..., 2)), total_discount (sum of discount, counting a missing discount as 0) and net_revenue (sum of subtotal minus discount). Sort by promo_group.

Starting Postgres in your browser…

Checking your access…