Lesson 3 of 4

Window functions: the interview favourite

2 min read

Window functions compute a value across related rows without collapsing them (unlike GROUP BY).

sql

function_name(...) OVER (PARTITION BY ... ORDER BY ... [frame])

Ranking

sql

SELECT job_id, student_id, score,
       ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY score DESC) AS rn,
       RANK()       OVER (PARTITION BY job_id ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY job_id ORDER BY score DESC) AS drnk
FROM applications WHERE score IS NOT NULL;

For scores 9, 8, 8, 7: ROW_NUMBER gives 1,2,3,4; RANK gives 1,2,2,4 (gap); DENSE_RANK gives 1,2,2,3 (no gap).

Top N per group

sql

-- Top 3 applicants per job
SELECT * FROM (
  SELECT a.*, ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY score DESC) AS rn
  FROM applications a WHERE score IS NOT NULL
) t WHERE rn <= 3;

Second highest salary (asked everywhere)

sql

SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM jobs
) t WHERE r = 2;

-- or without window functions
SELECT MAX(salary) FROM jobs WHERE salary < (SELECT MAX(salary) FROM jobs);

Running totals and moving averages

sql

SELECT applied_on::date AS day,
       COUNT(*) AS apps,
       SUM(COUNT(*)) OVER (ORDER BY applied_on::date) AS running_total,
       AVG(COUNT(*)) OVER (ORDER BY applied_on::date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM applications
GROUP BY applied_on::date
ORDER BY day;

Comparing with the previous row: LAG and LEAD

sql

-- Month-over-month growth in applications
WITH monthly AS (
  SELECT date_trunc('month', applied_on) AS month, COUNT(*) AS apps
  FROM applications GROUP BY 1
)
SELECT month, apps,
       LAG(apps) OVER (ORDER BY month) AS prev,
       ROUND(100.0 * (apps - LAG(apps) OVER (ORDER BY month)) / LAG(apps) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly;

sql

SELECT area, COUNT(*) AS jobs,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_all
FROM jobs GROUP BY area;

Practice

For each student, their latest application (use ROW_NUMBER ordered by applied_on DESC).
Day-wise new students with a 7-day rolling average.
Find consecutive days with at least one application (gaps and islands: date minus ROW_NUMBER).

← Previous Next →