Question 1

PARTITION BY: does the counter reset per group?

Accepted Answer

PARTITION BY dept resets the row number counter for each department. Within Eng, Alice (90000) gets 1, Bob (80000) gets 2. Within HR, Dave (75000) gets 1, Carol (70000) gets 2. PARTITION BY is like GROUP BY for window functions - it defines independent windows, one per partition value.

Question 2

ROW_NUMBER: what order determines the rank?

Accepted Answer

ROW_NUMBER() assigns a sequential integer to each row based on the ORDER BY inside OVER. Sorted by salary DESC: Alice (90000) = 1, Bob (80000) = 2, Carol (70000) = 3. Unlike GROUP BY, window functions do not collapse rows - every source row appears in the output.

Question 3

RANK vs ROW_NUMBER on ties

Accepted Answer

RANK assigns the same rank to tied rows and then skips the next rank(s). Alice and Bob both score 90 - they share rank 1. Because two rows share rank 1, rank 2 is skipped and Carol gets rank 3. DENSE_RANK would give Carol rank 2 instead (no gap). ROW_NUMBER would arbitrarily assign 1 to one of them and 2 to the other.

Question 4

DENSE_RANK on ties: what comes after two 1s?

Accepted Answer

DENSE_RANK assigns the same rank to tied rows but does not skip ranks. Alice and Bob both get 1. Carol gets 2 - the next consecutive rank, with no gap. Compare to RANK: RANK would give Carol 3 (skipping 2). Choose DENSE_RANK when gaps in rankings would confuse users, RANK when you need to show how many people scored above.

Question 5

SUM with an empty OVER clause: how many rows collapse?

Accepted Answer

SUM(amount) OVER () with an empty OVER clause aggregates over the entire result set. grand_total = 100+200+150 = 450 for every row. The key difference from GROUP BY: the original rows are preserved - each row keeps its individual amount AND gets the total appended. This makes it easy to compute each row's percentage of the total: amount / SUM(amount) OVER ().

Question 6

ORDER BY inside OVER: does SUM accumulate?

Accepted Answer

When you add ORDER BY inside OVER, the window frame defaults to 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'. This means SUM accumulates from the first row up to and including the current row - a running total. Jan: 100. Feb: 100+200=300. Mar: 100+200+150=450. This is one of the most common window function patterns.

Question 7

NTILE: which bucket does each row land in?

Accepted Answer

NTILE(n) splits the ordered rows into n roughly equal buckets, numbered 1 through n. With 4 rows and NTILE(2), bucket 1 gets the top 2 and bucket 2 gets the bottom 2. Alice (95) and Bob (80) land in bucket 1; Carol (70) and Dave (60) in bucket 2. NTILE is commonly used to assign quartiles (NTILE(4)), deciles (NTILE(10)), or percentile bands for scoring and segmentation.

Question 8

LAG with offset > 1

Accepted Answer

LAG(expr, offset) looks back offset rows in the window. For month 4, 2 rows back lands on month 2 with revenue=120. LAG(revenue, 1) would give month 3 (110). LAG(revenue) with no offset defaults to 1. You can also provide a third argument as the default for when no preceding row exists: LAG(revenue, 2, 0) would return 0 for months 1 and 2.

Question 9

Multiple window functions in one query

Accepted Answer

Window functions are independent: each one defines its own partition, order, and frame via its own OVER clause. A single SELECT can mix ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary), LAG(salary) OVER (ORDER BY hire_date), and SUM(sales) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) simultaneously. The WINDOW keyword (supported in PostgreSQL and others) lets you name a window spec and reuse it to avoid repetition when multiple functions share the same OVER clause.

Question 10

Window function vs GROUP BY

Accepted Answer

GROUP BY reduces N rows to one row per group - you lose access to individual row data. Window functions retain every source row and attach the aggregate as an extra column. Query A: 1 row per dept showing the department average. Query B: 1 row per employee showing their name, dept, AND the dept average - letting you immediately compare individual vs. group. This is why you cannot mix a window function and GROUP BY to do the same thing; they operate at different levels of granularity.

Question 11

NTILE with uneven bucket sizes

Accepted Answer

With 5 rows and NTILE(2), the buckets cannot be perfectly equal. SQL distributes the extra row to the earlier buckets: bucket 1 gets 3 rows (Alice, Bob, Carol), bucket 2 gets 2 rows (Dave, Eve). Dave is in bucket 2. The rule is that earlier buckets receive the extra rows when division is uneven - bucket 1 always gets ceil(n/k) rows. This can surprise you when you expect exactly equal splits; NTILE guarantees the sizes differ by at most 1.

Question 12

Can you mix window functions and GROUP BY?

Accepted Answer

Window functions are evaluated in the SELECT phase, which happens after GROUP BY has already reduced the rows. So by the time COUNT(*) OVER () runs, it sees one row per group - not the original employee rows. To count original rows per group alongside a GROUP BY aggregate, use COUNT(*) as a regular aggregate in the same GROUP BY query. If you need both individual rows and group aggregates, use a window function without GROUP BY - that is the whole point of PARTITION BY.

Question 13

Named windows with the WINDOW clause

Accepted Answer

PostgreSQL (and the SQL standard) support a WINDOW clause at the bottom of the query: SELECT ROW_NUMBER() OVER w, RANK() OVER w FROM t WINDOW w AS (PARTITION BY dept ORDER BY salary). Both functions share the same window spec 'w' without repeating it. This reduces errors when you refine the window - change it in one place and all functions pick up the update. The WINDOW clause is not supported in all databases (MySQL added it in 8.0; SQL Server does not support it as of 2022).

Question 14

What does PARTITION BY do in a window function?

Accepted Answer

PARTITION BY is like GROUP BY for window functions, but the key difference is that rows are never collapsed. Every input row stays in the output - the window function just resets its calculation at each partition boundary. For example, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) ranks employees within each department, not across the whole company.

Question 15

LAG: which row does it look back to?

Accepted Answer

LAG(amount) returns the value of amount from the previous row in the window order. Ordered by month (Jan, Feb, Mar), Feb's previous row is Jan with amount=100. Jan has no previous row, so LAG returns NULL for Jan. LAG is perfect for period-over-period comparisons: amount - LAG(amount) OVER (ORDER BY month) gives the month-over-month change.

Question 16

Top-N per group with window functions

Accepted Answer

The inner query assigns row numbers partitioned by dept, ordered by salary DESC. Within Eng: Alice=1, Bob=2. Within HR: Dave=1, Carol=2. The outer WHERE rn = 1 keeps only the top earner per department - Alice in Eng, Dave in HR. This 'top-N per group' pattern is one of the most common interview questions involving window functions.

Question 17

PERCENT_RANK: which direction puts Alice at 0.0?

Accepted Answer

PERCENT_RANK is calculated as (rank - 1) / (total_rows - 1). With ORDER BY score ASC, Carol is rank 1, Bob rank 2, Alice rank 3. But wait - Alice is the lowest rank in ASC order... actually Alice has the highest score so she is rank 3 in ASC. No: ASC means lowest first. Carol (60) = rank 1, Bob (80) = rank 2, Alice (100) = rank 3. Alice's PERCENT_RANK = (3-1)/(3-1) = 1.0. The bottom row always gets 0.0 (Carol here). Trick: ORDER BY score ASC means the highest score has PERCENT_RANK 1.0, not 0.0. To rank Alice at 0.0, use ORDER BY score DESC.

Question 18

NULLs in window ORDER BY

Accepted Answer

In PostgreSQL (and the SQL standard), NULLs sort as if they are larger than any non-NULL: NULLS LAST in ASC, NULLS FIRST in DESC. MySQL and SQL Server treat NULLs as smaller (NULLS FIRST in ASC). You can control this explicitly with ORDER BY col ASC NULLS LAST or ORDER BY col DESC NULLS LAST. This matters a lot for LAG/LEAD and ranking functions - a NULL at the start of the window means the first real row gets NULL from LAG even when you didn't expect it.

Question 19

Frame clauses: how many rows does 1 PRECEDING cover?

Accepted Answer

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW defines a frame that includes the previous row and the current row - exactly 2 rows. Day 1: only 1 row in frame (no predecessor) -> 10. Day 2: 10+20=30. Day 3: 20+30=50. Day 4: 30+40=70. Frame clauses give you precise control: UNBOUNDED PRECEDING gives a running total, 1 PRECEDING gives a rolling window, CURRENT ROW gives a single-row aggregate.

Question 20

FIRST_VALUE: same anchor row for every row?

Accepted Answer

FIRST_VALUE returns the value from the first row in the window frame. With ORDER BY salary DESC, the first row is Alice (90000). Every row in the result gets Alice in the highest_paid column because the default frame is RANGE UNBOUNDED PRECEDING - so the first row is always Alice. LAST_VALUE behaves differently: with the default frame it returns the current row's value, not the final row. To get the true last value, you need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Question 21

Why LAST_VALUE surprises developers

Accepted Answer

When you specify ORDER BY in OVER, the default window frame becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The 'last' row in that frame is always the current row itself - so LAST_VALUE returns the current row's value. To get the last row across the full partition, you must explicitly write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. FIRST_VALUE does not have this problem because the first row of 'UNBOUNDED PRECEDING to CURRENT ROW' is always the same partition-first row.

Question 22

Top-N per group: why a subquery is needed

Accepted Answer

SQL has a logical order of operations: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Window functions live in the SELECT phase, which means WHERE has already finished by the time rn is calculated. Referencing a window function alias in WHERE on the same level causes an error. Wrapping the windowed query in a subquery or CTE promotes it to a derived table, making rn a real column that the outer WHERE can see. This is why the top-N pattern always needs two levels of query.

Question 23

ROWS vs RANGE frame modes

Accepted Answer

With ROWS, '1 PRECEDING' means exactly the one row immediately before the current row in physical order - duplicates are treated separately. With RANGE, '1 PRECEDING' means all rows whose ORDER BY value falls within 1 unit of the current row's value - peers with the same value are all included together. This distinction matters most when you have ties. The default frame (RANGE UNBOUNDED PRECEDING AND CURRENT ROW) groups all ties with the current row, which is why running totals can look 'jumpy' when duplicates exist.

Question 24

What is a window frame?

Accepted Answer

A window frame narrows the calculation further inside each partition. The default frame for ORDER BY is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, meaning the function accumulates from the start of the partition up to the current row. You can override this - for example ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the frame to the current row plus the two rows before it, which is how a 3-row moving average works. Functions like ROW_NUMBER and RANK ignore the frame entirely; functions like SUM and AVG respect it.

Window Functions