Realistic Scenarios: Analysis and Optimisation

Scenario 1: the monthly sales report

Finance wants revenue, order count and average order value per month for 2024, with every month shown even if it had no orders, plus the change from the previous month.

WITH RECURSIVE months AS (
    SELECT DATE('2024-01-01') AS m
    UNION ALL
    SELECT DATE_ADD(m, INTERVAL 1 MONTH) FROM months WHERE m < '2024-12-01'
),
monthly AS (
    SELECT mo.m                          AS month_start,
           COUNT(o.id)                   AS orders,
           COALESCE(SUM(o.total), 0)     AS revenue
    FROM   months mo
    LEFT JOIN orders o
           ON o.order_date >= mo.m
          AND o.order_date <  DATE_ADD(mo.m, INTERVAL 1 MONTH)
          AND o.status <> 'cancelled'
    GROUP BY mo.m
)
SELECT month_start,
       orders,
       revenue,
       ROUND(revenue / NULLIF(orders, 0), 2)                       AS avg_order_value,
       LAG(revenue) OVER (ORDER BY month_start)                    AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month_start))
             / NULLIF(LAG(revenue) OVER (ORDER BY month_start), 0), 1) AS pct_change
FROM   monthly
ORDER BY month_start;

The three decisions that matter here: the cancelled filter is in the ON clause, not WHERE, so months with only cancelled orders still appear; the date join is a half open range on the bare column so an index is usable; and every division is guarded with NULLIF.

Scenario 2: customer retention

Marketing wants to know, for each signup month, how many customers went on to order in the following three months.

WITH cohort AS (
    SELECT id AS customer_id,
           DATE_FORMAT(signup_date, '%Y-%m') AS cohort_month,
           signup_date
    FROM   customers
),
activity AS (
    SELECT ch.cohort_month,
           ch.customer_id,
           TIMESTAMPDIFF(MONTH, ch.signup_date, o.order_date) AS months_after
    FROM   cohort ch
    JOIN   orders o ON o.customer_id = ch.customer_id
    WHERE  o.status <> 'cancelled'
)
SELECT cohort_month,
       COUNT(DISTINCT customer_id)                                          AS cohort_size,
       COUNT(DISTINCT CASE WHEN months_after = 0 THEN customer_id END)      AS month_0,
       COUNT(DISTINCT CASE WHEN months_after = 1 THEN customer_id END)      AS month_1,
       COUNT(DISTINCT CASE WHEN months_after = 2 THEN customer_id END)      AS month_2,
       COUNT(DISTINCT CASE WHEN months_after = 3 THEN customer_id END)      AS month_3
FROM   activity
GROUP BY cohort_month
ORDER BY cohort_month;

Scenario 3: a data quality audit

Before a migration, produce a report of every integrity problem in the schema.

-- Orphaned orders: a customer_id pointing at nothing
SELECT 'orphan_orders' AS issue, COUNT(*) AS rows_affected
FROM   orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE  c.id IS NULL

UNION ALL
-- Order totals that disagree with their line items
SELECT 'total_mismatch', COUNT(*)
FROM  (SELECT o.id
       FROM   orders o
       JOIN   order_items oi ON oi.order_id = o.id
       GROUP BY o.id, o.total
       HAVING o.total <> SUM(oi.quantity * oi.unit_price)) x

UNION ALL
-- Orders with no line items at all
SELECT 'empty_orders', COUNT(*)
FROM   orders o
WHERE  NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id)

UNION ALL
-- Employees whose manager does not exist
SELECT 'broken_manager_link', COUNT(*)
FROM   employees e
WHERE  e.manager_id IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM employees m WHERE m.id = e.manager_id)

UNION ALL
-- Duplicate customer emails, were the column not unique
SELECT 'duplicate_customers', COUNT(*)
FROM  (SELECT name, city FROM customers GROUP BY name, city HAVING COUNT(*) > 1) d;

Scenario 4: the duplicate cleanup

Two imports created duplicate customers by name and city. Keep the oldest, repoint the orders, remove the rest, and stop it recurring.

-- Step 1: inspect. Never skip this.
WITH dupes AS (
    SELECT id, name, city, signup_date,
           ROW_NUMBER() OVER (PARTITION BY name, city ORDER BY signup_date, id) AS rn,
           MIN(id)      OVER (PARTITION BY name, city)                          AS keep_id,
           COUNT(*)     OVER (PARTITION BY name, city)                          AS copies
    FROM   customers
)
SELECT * FROM dupes WHERE copies > 1 ORDER BY name, city, rn;

-- Step 2: repoint the children to the surviving parent
UPDATE orders o
JOIN  (SELECT c.id AS old_id, m.keep_id
       FROM   customers c
       JOIN  (SELECT name, city, MIN(id) AS keep_id
              FROM   customers GROUP BY name, city) m
              ON m.name = c.name AND m.city = c.city
       WHERE  c.id <> m.keep_id) map ON map.old_id = o.customer_id
SET    o.customer_id = map.keep_id;

-- Step 3: delete the losers
DELETE FROM customers
WHERE  id NOT IN (SELECT keep_id FROM (
           SELECT MIN(id) AS keep_id FROM customers GROUP BY name, city) k);

-- Step 4: make it impossible again
ALTER TABLE customers ADD CONSTRAINT uq_customers_name_city UNIQUE (name, city);

Scenario 5: the slow dashboard

A "top customers" panel takes eight seconds. Fix it.

-- The original: SELECT *, a correlated subquery per row, OFFSET paging
SELECT c.*,
       (SELECT SUM(total) FROM orders o WHERE o.customer_id = c.id) AS revenue
FROM   customers c
ORDER BY revenue DESC
LIMIT 20 OFFSET 0;

-- Rewritten: aggregate once, select only what is displayed
SELECT c.id, c.name, c.country, COALESCE(t.revenue, 0) AS revenue
FROM   customers c
LEFT JOIN (SELECT customer_id, SUM(total) AS revenue
           FROM   orders
           WHERE  status <> 'cancelled'
           GROUP BY customer_id) t ON t.customer_id = c.id
ORDER BY revenue DESC, c.id
LIMIT 20;

-- And the index that supports the aggregate
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status, total);

Three separate fixes: the correlated subquery becomes one grouped pass, SELECT * becomes four columns, and the index makes the aggregate an index only scan.

Scenario 6: deep pagination in an API

An orders endpoint slows to a crawl past page 500.

-- Before: cost grows with the page number
SELECT id, order_date, total FROM orders
ORDER BY order_date DESC, id DESC
LIMIT 20 OFFSET 10000;

-- After: constant cost per page, client passes the last row it saw
SELECT id, order_date, total FROM orders
WHERE  (order_date, id) < ('2024-03-19', 1002)
ORDER BY order_date DESC, id DESC
LIMIT 20;

CREATE INDEX idx_orders_keyset ON orders (order_date DESC, id DESC, total);

Check your understanding

1. In scenario 1, what breaks if the cancelled filter moves from ON to WHERE?
2. In scenario 4, why must step 2 run before step 3?
3. In scenario 5, which of the three changes would you expect to matter most, and how would you confirm it?