Aggregation, GROUP BY, and Window Functions

Summarize rows with aggregates and use window functions for rankings, running totals, and per-group comparisons.

Aggregate Functions

Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX summarize rows. Most aggregates ignore NULL inputs; COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column. GROUP BY creates groups, and HAVING filters groups after aggregation.

Be careful when joining before aggregating: a one-to-many join can duplicate rows and inflate sums. Aggregate each relationship at the correct level or use distinct/structured subqueries when appropriate. COALESCE can provide a fallback for a NULL aggregate result, but choose the fallback based on business meaning.

Revenue by customer

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total) AS revenue,
       AVG(total) AS average_order
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 10000
ORDER BY revenue DESC;

💡 The query calculates metrics per customer and keeps only groups whose total revenue exceeds 10000. HAVING filters grouped results, while WHERE filters individual rows before grouping.

Key Takeaways

COUNT(*) counts rows; COUNT(column) excludes NULL values.
WHERE filters rows before grouping; HAVING filters groups.
Check join cardinality before calculating sums.

Knowledge Check

Which clause filters groups after aggregation?

Window Functions and OVER

Window functions calculate values across related rows without collapsing them into one row per group. OVER defines the window; PARTITION BY divides rows into groups, and ORDER BY defines ordering within a window. Functions such as ROW_NUMBER, RANK, LAG, and LEAD support ranking and comparisons.

Unlike GROUP BY, window functions preserve the individual result rows. Understand window frames for running aggregates: the default frame can have peer-related behavior when ORDER BY is present, so specify a frame explicitly when the intended calculation requires row-by-row accumulation.

Rank orders per customer

SELECT customer_id, id, total,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY total DESC, id
       ) AS order_rank
FROM orders;

💡 ROW_NUMBER assigns a unique sequence within each customer's partition. The id tie-breaker makes the ordering deterministic when totals are equal.

Key Takeaways

Window functions preserve individual rows.
PARTITION BY defines independent groups for window calculations.
Use deterministic ordering for stable ranking.

Knowledge Check

Which window function assigns a unique sequential number to each row in a partition?

CTEs and Recursive Queries

A common table expression (CTE), introduced with WITH, names a subquery for use by the main statement. CTEs can improve readability and support multi-step transformations. Modern PostgreSQL may inline eligible CTEs; MATERIALIZED and NOT MATERIALIZED can influence planning in specific cases, so do not assume every CTE is always an optimization fence.

Recursive CTEs use WITH RECURSIVE to traverse hierarchical or graph-like data. A recursive query has a non-recursive anchor and a recursive term that references prior results. Ensure the recursion terminates, and consider cycle handling for data that may contain loops.

Recursive hierarchy traversal

WITH RECURSIVE team_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees AS e
  JOIN team_tree AS t ON e.manager_id = t.id
)
SELECT id, name, depth
FROM team_tree
ORDER BY depth, id;

💡 The anchor selects top-level employees. The recursive term finds employees whose manager is already in the tree, incrementing depth as it descends.

Key Takeaways

CTEs name subqueries and can improve readability.
Recursive CTEs traverse hierarchies and graph-like relationships.
Design recursion with termination and cycle behavior in mind.

Knowledge Check

What keyword enables a recursive CTE?

Aggregate Functions

Window Functions and OVER

CTEs and Recursive Queries

STAY UPDATED WITH BYTES!