Window Functions & Optimization — SQL Questions with Answers

Answer: A — Run EXPLAIN to see the execution plan and identify scans, missing indexes or bad join orders

Measure before changing. EXPLAIN (or EXPLAIN ANALYZE) shows exactly how the database runs the query, so you optimise the real bottleneck rather than guessing.

EXPLAINOptimisation

Answer: A — It transfers unneeded columns, prevents covering indexes, and breaks if the schema changes

Selecting only needed columns reduces I/O and network transfer and can let an index satisfy the query entirely (covering index). It also makes code resilient to column changes.

SELECT *Performance

Answer: A — The optimiser uses them to estimate row counts and choose good plans; stale stats lead to bad plans

After large data changes, running ANALYZE (or UPDATE STATISTICS) refreshes the numbers the optimiser relies on, helping it pick the right indexes and join methods.

StatisticsOptimiser

Answer: A — Pre-joining or pre-aggregating data reduces expensive joins at read time, at the cost of update complexity

For analytics dashboards, storing a summary table (updated periodically) avoids re-joining and re-aggregating huge tables on every query - a deliberate performance trade-off.

DenormalizationRead optimisation

SELECT name, dept, salary,
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

Answer: A — A function that computes across a set of rows RELATED to the current row, without collapsing them into one

Unlike aggregates with GROUP BY (which return one row per group), window functions add a computed column while KEEPING every row - perfect for running totals, ranks and comparisons to a group.

Window functionsOVER

Answer: A — The 'window' of rows the function operates on - via PARTITION BY and ORDER BY

OVER() is what makes a function a window function. Empty OVER() means the whole result set; PARTITION BY splits it into groups; ORDER BY orders rows within each partition.

OVERPARTITION BY

Answer: A — GROUP BY collapses rows into one per group; a window function keeps all rows and adds the computed value to each

To show each employee's salary alongside their department average WITHOUT losing individual rows, you need a window function. GROUP BY would leave you with one row per department.

Window functionsGROUP BY

Answer: A — Divides rows into groups; the function restarts its calculation for each partition

PARTITION BY dept means the window function (rank, running total, average) computes separately within each department, like a GROUP BY that keeps every row.

PARTITION BYOVER

Answer: A — A sequential number 1, 2, 3... assigned by descending marks - unique even for ties

ROW_NUMBER always gives distinct consecutive numbers, breaking ties arbitrarily. It is the tool for 'give me the top N per group' and for de-duplication.

ROW_NUMBERRanking

SELECT name, RANK() OVER (ORDER BY marks DESC) AS r,
            DENSE_RANK() OVER (ORDER BY marks DESC) AS dr
FROM students;

Answer: A — RANK leaves gaps after ties (1,1,3); DENSE_RANK does not (1,1,2)

With two rows tied at rank 1, RANK gives the next row rank 3 (skipping 2), while DENSE_RANK gives it rank 2. ROW_NUMBER would give 1 and 2 to the tied rows.

RANKDENSE_RANK

WITH r AS (
  SELECT *, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) rk FROM emp
)
SELECT * FROM r WHERE rk <= 3;

Answer: A — Use ROW_NUMBER/RANK OVER (PARTITION BY dept ORDER BY salary DESC) and filter <= 3 in an outer query

You cannot filter a window function in WHERE (it is computed after WHERE), so wrap it in a subquery/CTE and filter the rank there. This 'top N per group' is a classic window-function use.

Top N per groupPARTITION BY

Answer: A — Window functions are computed AFTER WHERE (during SELECT), so the value does not exist yet - use a subquery/CTE

Execution order places window functions after WHERE and GROUP BY. To filter on rank or a running total, compute it in a CTE and apply WHERE in the outer query.

Execution orderWindow functions

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

Answer: A — SUM(amount) OVER (ORDER BY date) - accumulates as rows progress

Adding ORDER BY to a windowed SUM makes it a running total (cumulative sum) up to the current row. PARTITION BY resets it per group (e.g. running total per account).

Running totalSUM OVER

SELECT month, sales,
  sales - LAG(sales) OVER (ORDER BY month) AS change
FROM monthly;

Answer: A — Access a value from a PREVIOUS (LAG) or NEXT (LEAD) row - useful for period-over-period comparisons

LAG(sales) OVER (ORDER BY month) gives last month's sales on the current row, so you can compute growth. LEAD looks forward. Both replace awkward self joins.

LAGLEAD

Answer: A — sales - LAG(sales) OVER (ORDER BY month) - compare each row to the previous

LAG pulls the prior month's value into the current row, so a simple subtraction gives the change. Divide by the lagged value for a percentage.

LAGGrowth

Answer: A — Divides ordered rows into 4 roughly equal buckets (quartiles), labelling each row 1-4

NTILE distributes rows into N groups - useful for quartiles, percentiles and cohort analysis. NTILE(100) approximates percentiles.

NTILEPercentiles

Answer: A — The first or last value in the window's ordered frame - e.g. the earliest order per customer

FIRST_VALUE(price) OVER (PARTITION BY product ORDER BY date) gives the earliest price on every row. LAST_VALUE needs a full frame clause to work as expected, a common gotcha.

FIRST_VALUELAST_VALUEFrame

AVG(price) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Answer: A — It defines which rows around the current row are included - e.g. a 3-row moving average

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW creates a moving window. Frames enable moving averages and custom cumulative ranges - a powerful, advanced feature.

Window frameMoving average

Answer: A — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - a running total up to the current row (including ties)

This default is why SUM(...) OVER (ORDER BY x) is a running total. It also explains the LAST_VALUE surprise: the default frame ends at the current row, not the partition end.

FrameDefault behaviour

Answer: A — ROW_NUMBER() OVER (PARTITION BY key ORDER BY created_at DESC) then keep rows where the number = 1

Numbering rows per key by recency and keeping rn = 1 selects the latest per group while retaining its other columns - which DISTINCT and GROUP BY cannot do cleanly.

ROW_NUMBERDeduplication

SELECT amount, amount * 100.0 / SUM(amount) OVER () AS pct FROM sales;

Answer: A — The grand total of x across ALL rows, repeated on every row

Empty OVER() treats the whole result set as one window. It is the neat way to add a grand total to every row - handy for computing each row's percentage of the total.

OVER()Percent of total

Answer: A — Adding an index on the columns used in WHERE/JOIN, and making conditions sargable

A missing index turning a full scan into an index seek can be orders of magnitude faster. Ensure conditions do not wrap the column in a function, or the index cannot be used.

IndexesSargability

Answer: A — With a matching index and ORDER BY, the database can stop after finding the first N rows instead of sorting everything

An index that already provides the requested order lets the engine read just the first N rows. Without the index it may sort the entire result before applying LIMIT.

LIMITIndexes

Answer: A — Running one query per row of a previous result (in application code) instead of a single join - very slow

Fetching 100 orders then querying each order's customer separately = 101 queries. A single JOIN (or IN clause) does it in one round trip. A common ORM performance trap.

N+1 problemJoins

Answer: A — When the condition wraps the column in a function, uses a leading wildcard LIKE '%x', or the query returns most rows

Non-sargable conditions (functions on the column), leading-wildcard searches, and low-selectivity queries all defeat indexes. EXPLAIN reveals when a scan is chosen instead.

SargabilityIndex usage

Answer: A — If materialised, it may prevent the optimiser from pushing filters down or using indexes across the boundary

In some databases a CTE is an optimisation fence. Readability is valuable, but for hot queries check EXPLAIN - occasionally inlining the logic lets the optimiser do more.

CTEOptimisation

Answer: A — Per-group ordered calculations - e.g. running total or rank that restarts for each partition

PARTITION BY account ORDER BY date with SUM gives a running balance PER account. The partition resets the calculation; the order defines the accumulation direction.

PARTITION BYRunning total

Answer: A — AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

The frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current day plus the six before it - a 7-row moving average. Frames make time-series smoothing straightforward.

Moving averageWindow frame

Answer: A — DENSE_RANK() OVER (ORDER BY value DESC) and filter for the rank = N

DENSE_RANK handles ties sensibly and generalises to any N, unlike nested MAX subqueries or LIMIT/OFFSET tricks. Filter the ranked result in an outer query.

DENSE_RANKNth highest

Answer: A — Measure with EXPLAIN, fix the real bottleneck (usually indexing and reducing rows early), then re-measure

Optimisation is empirical: profile the actual plan, address the biggest cost (a missing index, a full scan, fetching too much), and verify the improvement. Avoid premature or blind tuning.

OptimisationEXPLAIN