CTEs & Window Functions

13 min read

CTEs & Window Functions

Common Table Expressions (WITH Queries)

A CTE (Common Table Expression) lets you define a named temporary result set at the top of a query. This makes complex queries far more readable by breaking them into logical, named steps.

sql

-- Find the top 5 authors by published post count
WITH author_stats AS (
  SELECT
    author_id,
    COUNT(*) AS post_count
  FROM posts
  WHERE published = true
  GROUP BY author_id
),
top_authors AS (
  SELECT author_id, post_count
  FROM author_stats
  ORDER BY post_count DESC
  LIMIT 5
)
SELECT
  u.full_name,
  u.email,
  ta.post_count
FROM top_authors ta
JOIN users u ON u.id = ta.author_id;

Recursive CTEs

CTEs can be recursive, which is essential for traversing hierarchical data like category trees, org charts, or threaded comments.

sql

CREATE TABLE categories (
  id        int PRIMARY KEY,
  name      text,
  parent_id int REFERENCES categories(id)
);

-- Traverse the entire category tree from the root
WITH RECURSIVE tree AS (
  -- Base case: root categories
  SELECT id, name, parent_id, 0 AS depth, name::text AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive case: children
  SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || ' > ' || c.name
  FROM categories c
  JOIN tree t ON t.id = c.parent_id
)
SELECT depth, path FROM tree ORDER BY path;

Window Functions

Window functions perform calculations across a set of rows that are related to the current row, without collapsing rows like GROUP BY does. They are invaluable for rankings, running totals, and lag/lead comparisons.

sql

-- Rank posts by view count within each category
SELECT
  title,
  category,
  view_count,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY view_count DESC) AS row_num,
  RANK()       OVER (PARTITION BY category ORDER BY view_count DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY view_count DESC) AS dense_rank,
  SUM(view_count) OVER (PARTITION BY category)                       AS category_total,
  LAG(view_count)  OVER (PARTITION BY category ORDER BY view_count DESC) AS prev_views
FROM posts
WHERE published = true;

ROW_NUMBER() — assigns a unique number to each row; ties get different numbers.
RANK() — ties get the same number; the next rank skips (1, 1, 3).
DENSE_RANK() — ties get the same number; no gaps (1, 1, 2).
LAG(col, n) / LEAD(col, n) — access a value n rows before/after in the window.
SUM() / AVG() as window functions — running or partitioned totals without collapsing.

Window functions are evaluated after WHERE and GROUP BY but before ORDER BY and LIMIT. You can filter on window function results by wrapping the query in a CTE or subquery.

Ready to test yourself?

Practice PostgreSQL— quiz & coding exercises