Aggregation, Subqueries and CTEs

Aggregate functions

SELECT COUNT(*) AS orders,
       SUM(oi.quantity * oi.unit_cents) / 100.0 AS revenue,
       AVG(oi.unit_cents) / 100.0 AS avg_price
FROM order_items oi;

GROUP BY and HAVING

-- revenue per month, only months above $1,000
SELECT date_trunc('month', o.created_at) AS month,          -- strftime('%Y-%m', ...) in SQLite
       SUM(oi.quantity * oi.unit_cents) / 100.0 AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
GROUP BY month
HAVING SUM(oi.quantity * oi.unit_cents) > 100000
ORDER BY month;

WHERE filters rows before grouping; HAVING filters groups after. Every selected column must be grouped or aggregated.

Subqueries

-- products priced above the average
SELECT name, price_cents FROM products
WHERE price_cents > (SELECT AVG(price_cents) FROM products);

-- customers who ordered a hoodie
SELECT full_name FROM customers
WHERE id IN (
    SELECT o.customer_id FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    WHERE p.sku = 'HOOD-1'
);

EXISTS is often clearer and faster for "has at least one":

SELECT c.full_name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid');

CTEs: readable multi-step queries

WITH order_totals AS (
    SELECT order_id, SUM(quantity * unit_cents) AS cents
    FROM order_items GROUP BY order_id
),
customer_totals AS (
    SELECT o.customer_id, SUM(t.cents) AS cents, COUNT(*) AS orders
    FROM orders o JOIN order_totals t ON t.order_id = o.id
    GROUP BY o.customer_id
)
SELECT c.full_name, ct.orders, ct.cents / 100.0 AS spent
FROM customer_totals ct JOIN customers c ON c.id = ct.customer_id
ORDER BY spent DESC LIMIT 10;

Each step has a name, so complex reports stay readable and avoid the join-duplication trap.

Window functions

Calculations across related rows without collapsing them:

SELECT customer_id, id AS order_id, created_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS nth_order,
       SUM(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders_with_totals;

Use them for rankings, running totals, "latest row per group" and period-over-period comparisons (LAG()).

Exercise

Build a monthly report with a CTE: revenue, number of orders, number of distinct customers and the month-over-month change using LAG().

Check yourself

• What is the difference between WHERE and HAVING?
• When would you use EXISTS instead of IN?
• What does PARTITION BY do in a window function?