Chapter 18 Advanced 64 Questions

Practice Questions — Window Functions - ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

← Back to Notes

7 Easy

18 Medium

10 Hard

Topic-Specific Questions

Question 1

Easy

For salaries [90, 85, 85, 70], what does ROW_NUMBER() OVER (ORDER BY salary DESC) return for each row?

ROW_NUMBER is always unique and sequential.

1, 2, 3, 4

Question 2

Easy

For salaries [90, 85, 85, 70], what does RANK() OVER (ORDER BY salary DESC) return?

RANK skips after ties.

1, 2, 2, 4

Question 3

Easy

For salaries [90, 85, 85, 70], what does DENSE_RANK() OVER (ORDER BY salary DESC) return?

DENSE_RANK does not skip.

1, 2, 2, 3

Question 4

Easy

What does PARTITION BY do?

It divides rows into groups before the window function computes.

PARTITION BY splits the result set into groups. The window function restarts its computation at each group boundary — so ranking, running totals, and LAG/LEAD all reset per partition.

Question 5

Easy

What does LAG(amount) OVER (ORDER BY order_date) return for the FIRST row of the result set?

There is no row before the first one.

NULL

Question 6

Medium

Given amounts [100, 200, 300, 400, 500] ordered by date, what does SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) return for each row?

Running total from the start of the partition.

100, 300, 600, 1000, 1500

Question 7

Medium

Given amounts [10, 20, 30, 40, 50], what does AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) return for each row?

3-row moving average (includes current row).

10, 15, 20, 30, 40

Question 8

Medium

What is the default frame for OVER(ORDER BY date) with no explicit frame clause?

RANGE-based and ends at the current row.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Question 9

Medium

In NTILE(4) with 10 rows, how many rows does each bucket receive?

10 / 4 = 2 remainder 2. Extras go to the first buckets.

Buckets 1 and 2: 3 rows each. Buckets 3 and 4: 2 rows each.

Question 10

Medium

Can a window function appear in the WHERE clause?

Order of evaluation matters.

No

Question 11

Medium

For values [10, 20, 30, 40], what does LEAD(x, 1, -1) OVER (ORDER BY id) return?

LEAD looks forward. Default is -1 if no next row.

20, 30, 40, -1

Question 12

Medium

PERCENT_RANK() OVER (ORDER BY salary) for 5 rows returns what for the first and last row?

Formula: (rank - 1) / (total_rows - 1).

First: 0.00, Last: 1.00

Question 13

Hard

Given customer_id partitions [A, A, B, B, B] in that order, what does ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date) return?

Row numbers restart at each partition.

1, 2, 1, 2, 3

Question 14

Hard

What will LAST_VALUE(x) OVER (ORDER BY date) return without an explicit frame?

Default frame ends at CURRENT ROW.

The current row's x value (not the last partition value)

Question 15

Hard

For the sequence [100, 120, 95, 130], what does 120 - LAG(120) OVER (ORDER BY id) compute? And what if 120's LAG is NULL?

Arithmetic with NULL.

difference with previous row; returns NULL when LAG is NULL

Question 16

Medium

When would you use RANK vs DENSE_RANK?

Think about how gaps in the ranking affect downstream logic.

Use RANK when you want 'position in the ordering', including the effect of ties — 'the 4th runner on the leaderboard' even though positions 1-3 tied. Use DENSE_RANK when you want to count DISTINCT values — 'the 3rd highest distinct salary' or to fetch 'top N distinct categories'. For top-N per group queries, DENSE_RANK typically returns more rows when ties occur at the threshold.

Question 17

Hard

Why is ROWS BETWEEN almost always preferred over RANGE BETWEEN?

Think about how each counts.

ROWS counts physical rows (mechanical, predictable). RANGE counts by value — it includes all rows whose ORDER BY value equals the current row's value plus the offset. With RANGE, ties cause surprising inclusions; a 2-row moving average could include 5 rows if they all tie. ROWS gives exactly what you ask for. Use RANGE only when you explicitly want time-value-based windows like 'all orders in the last 7 days regardless of how many rows that is'.

Question 18

Medium

Given employees(name, dept, salary), return only the highest-paid employee per dept. Ties count (include all tied top earners).

DENSE_RANK with partition, then filter rnk = 1.

SELECT name, dept, salary
FROM (
  SELECT name, dept, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 1;

Question 19

Medium

Given sales(sale_date, amount), return each day's sale and the running total.

SUM OVER with an ordered frame.

SELECT sale_date, amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS UNBOUNDED PRECEDING
  ) AS running_total
FROM sales
ORDER BY sale_date;

Question 20

Medium

Given orders(customer_id, order_date, amount), return each order along with the previous order date for the same customer.

LAG with PARTITION BY customer_id.

SELECT customer_id, order_date, amount,
  LAG(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS prev_order_date
FROM orders
ORDER BY customer_id, order_date;

Question 21

Hard

Given orders(order_date, amount), return month, revenue, and month-over-month growth percentage.

Aggregate to monthly totals first, then apply LAG.

WITH monthly AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
         SUM(amount)                      AS revenue
  FROM orders
  GROUP BY month
)
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month) * 100, 2
  ) AS mom_pct
FROM monthly
ORDER BY month;

Question 22

Hard

Write a query to find the 3rd highest distinct salary from employees(salary).

DENSE_RANK = 3.

SELECT DISTINCT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 3;

Question 23

Hard

Given logins(user_id, login_date), identify consecutive login streaks per user using the 'gaps and islands' trick.

Subtract ROW_NUMBER * 1 day from login_date; consecutive dates share the same grouping key.

SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS streak_length
FROM (
  SELECT user_id, login_date,
         DATE_SUB(login_date,
                  INTERVAL ROW_NUMBER() OVER
                    (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM logins
) t
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

Question 24

Medium

Given sales(sale_date, amount), compute a 7-day trailing moving average.

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.

SELECT sale_date, amount,
  ROUND(AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS ma_7d
FROM sales
ORDER BY sale_date;

Mixed & Application Questions

Question 1

Easy

What is returned by AVG(salary) OVER () — with empty OVER parentheses?

No partition, no order — one single window.

The global average salary, repeated on every row.

Question 2

Easy

For 5 rows with scores [80, 90, 70, 85, 95], what does ROW_NUMBER() OVER (ORDER BY score DESC) return for the row with score 85?

Count ranks down from 95.

3

Question 3

Medium

Predict the running total of 500, 300, 200, 400 in that order using SUM OVER (ORDER BY id ROWS UNBOUNDED PRECEDING):

Sum each row with all earlier rows.

500, 800, 1000, 1400

Question 4

Medium

For customer_id [1,1,2,2,2] ordered by date, what does ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date) return?

Each partition restarts.

1, 2, 1, 2, 3

Question 5

Medium

What is the output of SUM(CASE WHEN status='paid' THEN amount END) OVER (PARTITION BY customer_id) in a mix of paid/failed orders?

CASE inside a window aggregate.

The per-customer total of only 'paid' amounts, on every row for that customer.

Question 6

Medium

Given employees(name, dept, salary), add a column showing the difference between each employee's salary and the department average.

AVG OVER with PARTITION BY dept.

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

Question 7

Medium

Given orders(customer_id, order_date, amount), show each order with the customer's cumulative spend up to that order.

PARTITION BY customer_id with running total frame.

SELECT customer_id, order_date, amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS UNBOUNDED PRECEDING
  ) AS cumulative_spend
FROM orders
ORDER BY customer_id, order_date;

Question 8

Hard

Given exam(student, score), return student, score, and their percentile (0-100).

PERCENT_RANK returns 0..1; multiply by 100.

SELECT student, score,
  ROUND(PERCENT_RANK() OVER (ORDER BY score) * 100, 2) AS percentile
FROM exam
ORDER BY score DESC;

Question 9

Hard

Given logins(user_id, login_date), find the longest login streak per user.

Gaps-and-islands trick, then GROUP BY + MAX.

WITH streaks AS (
  SELECT user_id, login_date,
    DATE_SUB(login_date,
             INTERVAL ROW_NUMBER() OVER
               (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM logins
),
streak_lengths AS (
  SELECT user_id, grp, COUNT(*) AS streak_length
  FROM streaks
  GROUP BY user_id, grp
)
SELECT user_id, MAX(streak_length) AS longest_streak
FROM streak_lengths
GROUP BY user_id;

Question 10

Medium

Why does SUM(amount) OVER () appear on every row with the grand total?

Empty OVER() = one window = whole result set.

OVER() with no PARTITION BY, ORDER BY, or frame treats the entire result set as a single window. The aggregate computes once over all rows, and the result is projected onto every row. This is the standard way to compute 'grand total' or 'overall average' alongside row-level data without a second scan of the table.

Question 11

Hard

In MySQL, when would you prefer a window function over a correlated subquery?

Think about performance and readability.

Window functions are almost always better than correlated subqueries when both can express the same logic. Reasons: (1) Window functions scan the table once and compute all ranking/running values in a single pass. Correlated subqueries often rerun for each row, making them O(n^2). (2) Window functions are more readable — the intent is obvious. (3) The optimizer has better statistics and plan options for window functions. Use correlated subqueries only when window functions cannot express the logic (rare).

Multiple Choice Questions

MCQ 1

Which keyword introduces the window specification?

A. WINDOW
B. OVER
C. WITHIN
D. PARTITION

Answer: B
B is correct. The OVER clause defines the window. PARTITION BY and ORDER BY live inside OVER(). There is a WINDOW clause for named windows but it is rarely used.

MCQ 2

Which function gives a unique sequence (no ties)?

A. RANK
B. DENSE_RANK
C. ROW_NUMBER
D. NTILE

Answer: C
C is correct. ROW_NUMBER always returns 1, 2, 3, ... with no ties. RANK and DENSE_RANK share numbers on ties.

MCQ 3

For salaries [90, 90, 80], DENSE_RANK() DESC returns:

A. 1, 2, 3
B. 1, 1, 2
C. 1, 1, 3
D. 2, 2, 3

Answer: B
B is correct. DENSE_RANK gives both 90s rank 1 (tied), then the next distinct value (80) gets rank 2 — no gap.

MCQ 4

PARTITION BY in OVER() does what?

A. Divides rows into groups so the function restarts per group
B. Filters rows
C. Sorts the output
D. Creates a named window

Answer: A
A is correct. PARTITION BY is like GROUP BY for windows — each group is an independent window, and the function restarts at each boundary.

MCQ 5

LAG(col, 2, 0) returns:

A. The value 2 rows before the current row, or 0 if no such row
B. The value 2 rows after
C. 0 always
D. An error

Answer: A
A is correct. Arguments: (column, offset, default). Offset 2 means 2 rows back. Default 0 is returned when the offset would fall before the partition start.

MCQ 6

Which clauses can contain a window function?

A. WHERE and HAVING only
B. SELECT and ORDER BY only
C. SELECT, WHERE, and GROUP BY
D. Everywhere

Answer: B
B is correct. Window functions are computed AFTER WHERE/GROUP BY/HAVING. They can only appear in SELECT and ORDER BY. To filter on a window result, wrap the query in a subquery or CTE.

MCQ 7

Which frame gives a 7-row trailing moving average (current row + 6 before)?

A. ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
B. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
C. ROWS BETWEEN UNBOUNDED PRECEDING AND 7 PRECEDING
D. RANGE BETWEEN 7 DAY PRECEDING AND CURRENT ROW

Answer: B
B is correct. 6 preceding + current row = 7 rows total. Option A would give 8 rows.

MCQ 8

Why does LAST_VALUE() commonly return the wrong answer without an explicit frame?

A. A MySQL bug
B. The default frame ends at CURRENT ROW
C. It can only be used with PARTITION BY
D. It requires DISTINCT

Answer: B
B is correct. The default frame (with ORDER BY) is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Fix with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

MCQ 9

NTILE(4) with 9 rows distributes rows how?

A. All buckets get 2 rows, last gets 3
B. Buckets 1 gets 3, buckets 2-4 each get 2
C. Each gets 2 except one with 1
D. Rows are randomly distributed

Answer: B
B is correct. 9 / 4 = 2 remainder 1. The first 1 bucket gets an extra row. So bucket 1 has 3, buckets 2, 3, 4 each have 2.

MCQ 10

To find 'top 3 per department' including ties, which is best?

A. ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) and filter rn <= 3
B. DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) and filter rnk <= 3
C. NTILE(3)
D. LIMIT 3 OFFSET 0

Answer: B
B is correct. DENSE_RANK <= 3 gives the top 3 distinct salary levels per dept, including all ties. ROW_NUMBER would cut off ties arbitrarily. NTILE does bucketing, not top-N. LIMIT applies to the whole query, not per group.

MCQ 11

Which version introduced window functions in MySQL?

A. 5.7
B. 8.0
C. 5.6
D. They were always there

Answer: B
B is correct. MySQL added window functions in 8.0. Earlier versions required user-defined variable hacks or correlated subqueries. MariaDB 10.2+ also supports them.

MCQ 12

SUM(amount) OVER () without any PARTITION BY or ORDER BY returns:

A. A running total
B. The grand total, repeated on every row
C. NULL
D. An error

Answer: B
B is correct. Empty OVER() treats the whole result as one window. SUM over that = grand total on every row — useful for 'percent of total' calculations.

MCQ 13

Why is ROWS usually preferred over RANGE in frame clauses?

A. ROWS counts physical rows deterministically; RANGE can include unexpected ties
B. RANGE is deprecated
C. ROWS is faster always
D. RANGE only works with DATE columns

Answer: A
A is correct. RANGE uses value-based frames, so ties can pull in more rows than you expect. ROWS is mechanical and predictable. Use RANGE only when you specifically want time-value windows.

MCQ 14

What's the classic 'gaps and islands' trick for consecutive dates?

A. Subtract ROW_NUMBER from the date; same result = same streak
B. Use LAG and check if diff = 1
C. Group by date
D. GROUP BY ROLLUP

Answer: A
A is correct. date - row_number is constant for consecutive rows. Grouping by that key gives you each streak. B (LAG + diff) also works but requires more steps; A is the elegant one-shot key.

MCQ 15

To filter on a window function result, you must:

A. Use the window function in WHERE
B. Wrap the query in a subquery or CTE and filter the outer query
C. Use HAVING
D. Use GROUP BY

Answer: B
B is correct. Window functions are evaluated after WHERE/GROUP BY/HAVING, so you cannot filter on their results in the same level. Subquery or CTE is the fix.

MCQ 16

LAG(col) OVER (ORDER BY id) for the first row returns:

A. 0
B. NULL
C. The same value as the first row
D. Error

Answer: B
B is correct. No previous row exists, so LAG defaults to NULL unless you pass a 3rd argument as the default.

MCQ 17

Which query gives each row's amount as a percentage of the grand total?

A. amount / SUM(amount) * 100
B. amount / SUM(amount) OVER () * 100
C. amount / SUM(amount) OVER (PARTITION BY id) * 100
D. PERCENT_RANK()

Answer: B
B is correct. Empty OVER() gives the grand total on every row. Option A is invalid (plain SUM without GROUP BY). Option C partitions per-id, giving 100% per row.

MCQ 18

What is the key difference between LAG and subquery '(SELECT col FROM t WHERE id = outer.id - 1)'?

A. LAG is faster and more readable; subquery is O(n^2) correlated
B. They are equivalent in all cases
C. Subquery is faster
D. LAG doesn't exist in MySQL 8

Answer: A
A is correct. LAG computes in a single window-function pass. A correlated subquery reruns for each row. On large tables, LAG can be 100x faster.

MCQ 19

To include ties when finding 'top 3 employees per dept', use:

A. ROW_NUMBER <= 3
B. RANK <= 3
C. DENSE_RANK <= 3
D. Both B and C work, but they return slightly different sets when ties straddle the boundary

Answer: D
D is correct. RANK <= 3 gives top 3 positions including ties at any rank. DENSE_RANK <= 3 gives top 3 distinct salary levels. Both include ties; which is correct depends on wording. ROW_NUMBER arbitrarily cuts ties.

MCQ 20

For a running total per customer, the correct OVER clause is:

A. OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING)
B. OVER (PARTITION BY customer_id ORDER BY order_date ROWS UNBOUNDED PRECEDING)
C. OVER (PARTITION BY customer_id)
D. OVER ()

Use gaps-and-islands: subtract ROW_NUMBER days from login_date to get a streak key.

CREATE TABLE logins (user_id INT, login_date DATE);
INSERT INTO logins VALUES
  (1,'2026-04-10'),(1,'2026-04-11'),(1,'2026-04-12'),
  (1,'2026-04-15'),(1,'2026-04-16'),
  (2,'2026-04-10'),(2,'2026-04-12'),(2,'2026-04-13'),(2,'2026-04-14');

WITH marked AS (
  SELECT user_id, login_date,
    DATE_SUB(login_date,
             INTERVAL ROW_NUMBER() OVER
               (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM logins
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS streak_length
FROM marked
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass