SQL Window Functions and CTEs in PostgreSQL

Window functions compute values across related rows without collapsing them like GROUP BY does. Combined with CTEs, they replace a lot of application-side data crunching.

Window function basics

SELECT customer_id, id AS order_id, total_paise,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS order_number,
       SUM(total_paise) OVER (PARTITION BY customer_id) AS lifetime_value
FROM orders;

• PARTITION BY - the group each row is compared within.
• ORDER BY inside OVER - the order within the partition.

Top N per group

SELECT * FROM (
  SELECT p.*, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
  FROM products p
) ranked
WHERE rn <= 3;      -- best 3 sellers in each category

Running totals and comparisons

SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total,
       revenue - LAG(revenue) OVER (ORDER BY day) AS change_vs_yesterday,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_revenue;

CTEs for readable queries

WITH first_orders AS (
  SELECT customer_id, MIN(created_at) AS first_at
  FROM orders GROUP BY customer_id
),
cohorts AS (
  SELECT date_trunc('month', first_at) AS cohort, customer_id FROM first_orders
)
SELECT cohort, COUNT(*) AS new_customers
FROM cohorts
GROUP BY cohort
ORDER BY cohort;

Recursive CTEs for trees

-- All sub-categories under category 5, at any depth
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth FROM categories WHERE id = 5
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

RANK() leaves gaps after ties (1, 1, 3); DENSE_RANK() doesn't (1, 1, 2). Pick the one that matches what "position" means in your report.