sql

🔷 SQL: The CTE Pattern (and why you need it)

A CTE (Common Table Expression) is a named temporary result you define with WITH. You NEED CTEs because you cannot filter window functions in WHERE.

-- "Top 2 products by spending per category"
WITH ranked AS (
  SELECT category, product, SUM(spend) AS total_spend,
    RANK() OVER (PARTITION BY category ORDER BY SUM(spend) DESC) AS ranking
  FROM product_spend
  GROUP BY category, product
)
SELECT category, product, total_spend
FROM ranked
WHERE ranking <= 2;

Why the CTE is mandatory: Window functions compute during SELECT. WHERE runs before SELECT. So you can't write WHERE RANK() OVER (...) <= 2. The CTE computes the rank first, then the outer query filters on it.