SQLRepPracticeMock interviewGuidesPlaygroundLearning path
Interview questionsPostgres errorsCheat sheetsTermsPrivacyRefundssupport@sqlrep.com
© SQLRep · Practice SQL online, right in your browser.

Cheat sheets · Module 4

Window Functions cheat sheet

Solve the window-function questions most SQL interviews ask.

Window functions · DENSE_RANK()

Window functions compute a value for each row from a 'window' of related rows, without collapsing them like GROUP BY. PARTITION BY restarts the calculation per group. DENSE_RANK gives ties the same rank with no gaps (1, 2, 2, 3); RANK leaves gaps (1, 2, 2, 4); ROW_NUMBER never ties.

SELECT name, score,
  DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS rnk
FROM players;
Practice: Top 3 earners per department →

AVG() OVER · compare to group

A window aggregate like AVG(salary) OVER (PARTITION BY department) puts the group's average on every row, so you can compare each row with its own group. A correlated subquery works too, but the window version reads more cleanly.

SELECT name, score,
  AVG(score) OVER (PARTITION BY class) AS class_avg
FROM students;
Practice: Paid above department average →

SUM() OVER () · percent of total

An empty window, OVER (), spans the whole result, so SUM(x) OVER () puts the grand total on every row. After a GROUP BY you can even wrap an aggregate: SUM(SUM(x)) OVER () is the total of all the group sums.

SELECT region, SUM(sales) AS sales,
  ROUND(100.0 * SUM(sales) / SUM(SUM(sales)) OVER (), 1) AS pct
FROM orders
GROUP BY region;
Practice: Share of total spend →

SUM() OVER · running totals

Putting ORDER BY inside OVER() turns an aggregate into a running calculation: SUM(x) OVER (ORDER BY day) is the total of x from the first row up to the current one. Every row is kept.

SELECT day, signups,
  SUM(signups) OVER (ORDER BY day) AS cumulative
FROM daily_signups;
Practice: Running revenue total →

LAG() · date arithmetic

LAG(col) returns col from the previous row in the window and LEAD(col) from the next. With PARTITION BY user_id ORDER BY login_date, LAG(login_date) is that user's previous login. In Postgres, subtracting two DATE values gives the gap in whole days.

SELECT day, price,
  price - LAG(price) OVER (ORDER BY day) AS change
FROM stock_prices;
Practice: Consecutive-day logins →

LEAD() · PARTITION BY

LEAD(col) reads col from the next row of the window, LAG(col) from the previous one. With PARTITION BY customer_id ORDER BY order_date, LEAD(order_date) is that customer's next order date, and the customer's last row gets NULL because there is no next row.

SELECT patient_id, visit_date,
  LEAD(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit
FROM visits;
Practice: Days until next order →

ROW_NUMBER() · deterministic tie-breaker

For exactly one row per group, number the rows with ROW_NUMBER() OVER (PARTITION BY group ORDER BY ...) and keep row 1. If two rows tie on the sort, Postgres may pick either one, so add a tie-breaker column to the window's ORDER BY to make the answer repeatable.

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY store_id ORDER BY total DESC, receipt_id DESC) AS rn
  FROM receipts
) AS r
WHERE rn = 1;
Practice: Largest charge per account →

Nth highest · DENSE_RANK() vs ROW_NUMBER()

'Nth highest salary' means the Nth distinct salary, so people on the same pay must not use up a place. DENSE_RANK() gives equal values the same rank with no gaps, so rank = N is exactly the Nth distinct value; ROW_NUMBER() counts people and RANK() skips numbers after a tie. A group with fewer than N distinct values has no answer at all.

SELECT DISTINCT league, points
FROM (
  SELECT league, points,
    DENSE_RANK() OVER (PARTITION BY league ORDER BY points DESC) AS r
  FROM teams
) AS t
WHERE r = 2;
Practice: Third-highest salary per department →

DISTINCT ON · first row per group

Postgres's DISTINCT ON (key) keeps the first row of each key, and ORDER BY decides which row is first. The ORDER BY must start with the same key, then the column that picks the winner: DISTINCT ON (team) ... ORDER BY team, score DESC keeps each team's top scorer, with all its columns.

SELECT DISTINCT ON (device_id) device_id, reading, recorded_at
FROM sensor_readings
ORDER BY device_id, recorded_at DESC;
Practice: Each customer's first order →

NTILE() · buckets

NTILE(n) OVER (ORDER BY x) deals the rows into n numbered buckets as evenly as possible, in sort order. When the rows don't divide evenly, the first buckets get one extra row. NTILE(4) gives quartiles, NTILE(10) deciles.

SELECT name, score,
  NTILE(10) OVER (ORDER BY score DESC) AS decile
FROM students;
Practice: Salary quartiles →

Window frames · ROWS vs RANGE

A window frame picks which rows an aggregate sees. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means this row and the two physically before it. Without a frame clause, ORDER BY inside OVER defaults to RANGE UNBOUNDED PRECEDING: everything so far, plus every row tied with the current one (its 'peers').

SELECT day, temp,
  AVG(temp) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM weather;
Practice: 3-transaction moving average →

FIRST_VALUE · LAST_VALUE · frame trap

FIRST_VALUE(x) and LAST_VALUE(x) return x from the first and last row of the window frame. The trap: with ORDER BY, the default frame ends at the current row, so LAST_VALUE just returns the current row's own value. Widen the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (or use FIRST_VALUE with ORDER BY ... DESC).

SELECT DISTINCT player_id,
  LAST_VALUE(rating) OVER (
    PARTITION BY player_id ORDER BY played_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS current_rating
FROM matches;
Practice: First vs latest order value →

Practice every concept interactively at sqlrep.com