CTEs and Window Functions

WITH paid AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT user_id, count(*) FROM paid GROUP BY user_id;
-- counts paid orders

WITH RECURSIVE walk AS (
  SELECT id, parent_id, 1 AS depth FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id, w.depth + 1
  FROM nodes n
  JOIN walk w ON n.parent_id = w.id
)
SELECT * FROM walk;
-- subtree under id=1

SELECT id, user_id,
  row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM events;
-- rn=1 is latest per user

SELECT dept, name, salary,
  avg(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;
-- dept_avg constant within dept

SELECT day, revenue,
  lag(revenue) OVER (ORDER BY day) AS prev
FROM daily;
-- prev: prior day revenue

SELECT day,
  sum(n) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) AS running
FROM stats;
-- cumulative n

SELECT score,
  rank() OVER (ORDER BY score DESC) AS r,
  dense_rank() OVER (ORDER BY score DESC) AS dr
FROM scores;

SELECT id, ntile(4) OVER (ORDER BY score) AS quartile
FROM players;
-- quartile 1..4

SELECT count(*) FILTER (WHERE ok) OVER () AS ok_count FROM t;

WITH c AS MATERIALIZED (
  SELECT * FROM big WHERE x > 1
)
SELECT count(*) FROM c;
-- force materialize

WITH d AS (
  DELETE FROM sessions WHERE expired RETURNING user_id
)
SELECT user_id, count(*) FROM d GROUP BY 1;
-- expired session counts

SELECT id,
  first_value(price) OVER (PARTITION BY sku ORDER BY ts) AS first_price
FROM prices;

SELECT sum(n) OVER (
  ORDER BY i
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  EXCLUDE CURRENT ROW
) FROM t;

SELECT id,
  avg(v) OVER w AS avg3
FROM t
WINDOW w AS (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);

SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY score DESC) rn
  FROM scores
) s WHERE rn <= 3;
-- top 3 scores per user