Unit 06.04: When a CTE changes meaning, not just readability

Unit ID: SQL-M06-U05 - Estimated active time: 13-16 minutes Objective: identify refactors that alter results, and verify equivalence rather than assuming it.

Most CTE refactors are cosmetic. Some are not.

Moving logic into a CTE changes the answer whenever it changes when an operation happens - especially aggregation relative to a join.

The pair from Module 5, side by side

-- Aggregate AFTER joining: fan-out inflates revenue
SELECT SUM(o.order_total) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;
-- 9147789.00

-- Aggregate BEFORE joining: correct
WITH items AS (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
)
SELECT SUM(o.order_total) AS revenue
FROM orders o
LEFT JOIN items i ON i.order_id = o.order_id;
-- 2701463.00

The CTE is not tidying here. It is changing the grain the join operates on, and therefore the answer - by ₹64.5 lakh.

The check that proves a refactor is safe

Never assume a rewrite preserved meaning. Compare directly:

WITH original AS (
  SELECT COUNT(*) AS n, SUM(order_total) AS total
  FROM orders WHERE status = 'completed'
),
refactored AS (
  SELECT COUNT(*) AS n, SUM(order_total) AS total
  FROM (SELECT * FROM orders WHERE status = 'completed') t
)
SELECT o.n = r.n AS counts_match,
       o.total = r.total AS totals_match
FROM original o CROSS JOIN refactored r;
-- true | true

Two booleans, and the equivalence is now evidence rather than belief.

Filter placement inside a CTE

Where a filter sits relative to an aggregate changes what the aggregate covers:

-- Filter before aggregating: average of completed orders only
WITH c AS (SELECT * FROM orders WHERE status = 'completed')
SELECT ROUND(AVG(order_total), 2) FROM c;
-- 2718.53

-- Aggregate first, filter after: average across everything
WITH a AS (SELECT status, AVG(order_total) AS avg_total FROM orders GROUP BY status)
SELECT ROUND(avg_total, 2) FROM a WHERE status = 'completed';
-- 2718.53

These agree here because the grouping key is the filter column. Change the filter to something not in the GROUP BY and they diverge immediately - which is the general warning.

Practice

Refactor the naive fan-out query into a CTE version, then prove the two disagree and state which is right.

WITH items AS (
  SELECT order_id, COUNT(*) AS item_count FROM order_items GROUP BY order_id
),
correct AS (
  SELECT SUM(o.order_total) AS revenue
  FROM orders o LEFT JOIN items i ON i.order_id = o.order_id
),
naive AS (
  SELECT SUM(o.order_total) AS revenue
  FROM orders o JOIN order_items i ON i.order_id = o.order_id
)
SELECT c.revenue AS correct_revenue,
       n.revenue AS naive_revenue,
       n.revenue - c.revenue AS inflation
FROM correct c CROSS JOIN naive n;
-- 2701463.00 | 9147789.00 | 6446326.00

Takeaway

A CTE that moves an aggregation across a join changes the answer, not the formatting. Prove equivalence with a comparison query instead of trusting that a refactor was neutral.

---

Mark complete