Window Functions and CTEs

The advanced SQL that turns reports into one query

Window functions compute values across rows related to the current row, without collapsing them into groups. CTEs (Common Table Expressions) let you name and reuse subqueries — and recursive CTEs handle hierarchical data.

Window function shapes you'll use:

row_number() OVER (ORDER BY ...) — sequential number per ordering.
rank() / dense_rank() — competition-style ranking.
sum(...) OVER (...) / avg(...) OVER (...) — running totals, moving averages.
lag(col) / lead(col) — previous/next row's value.
partition by — windows reset per partition.

Tip: Recursive CTE is SQLite's general tool for hierarchies: org charts, file trees, comment threads. The shape is always WITH RECURSIVE name AS (anchor UNION ALL recursive_step) SELECT .... Once you internalize the pattern it replaces a lot of application-side traversal code.

Code

Window function — running message count per conversation·sql

SELECT id, conversation_id, role, created_at,
       row_number() OVER (PARTITION BY conversation_id
                          ORDER BY created_at) AS msg_index,
       count(*)     OVER (PARTITION BY conversation_id) AS conv_total
FROM   messages
ORDER  BY conversation_id, created_at;

Recursive CTE — manager chain·sql

WITH RECURSIVE chain(id, name, manager_id, depth) AS (
  SELECT id, name, manager_id, 0
  FROM   employees
  WHERE  id = 42                              -- starting employee
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM   employees e
  INNER JOIN chain c ON e.id = c.manager_id
)
SELECT depth, name FROM chain ORDER BY depth;
-- 0 | Alice (the IC)
-- 1 | Bob (her manager)
-- 2 | Carol (his manager)
-- ...up to the CEO

Exercise

On the messages table, write three window-function queries: (1) per-conversation running total of message length, (2) the time gap between consecutive messages in each conversation using lag, (3) top-3 longest messages per conversation using row_number in a subquery. Then build the manager-chain recursive CTE on a real org-chart shape.

Window Functions and CTEs

The advanced SQL that turns reports into one query

Code

External links

Exercise

Progress

Comments 0