AI InterviewSearch…Search problems, topics, or pages…Ctrl KGet started
Skip to content

10 Window functions / Checkpoint

Checkpoint — Window functions

25 min · +25 XP · 3 exercises · 12 questions

Exercises0/3
Quiz · 70% to clear12 q

This checkpoint covers the whole module: window functions with OVER, partitions and the whole-result window OVER (), where windows sit in the order of evaluation, ROW_NUMBER, RANK, DENSE_RANK and NTILE and how they treat ties, LAG, LEAD and FIRST_VALUE, frames with ROWS and RANGE, running totals and moving averages, and the top-N-per-group pattern.

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

  • What is the difference between GROUP BY dept and PARTITION BY dept?
  • For scores 90, 90, 80, what do ROW_NUMBER, RANK and DENSE_RANK give?
  • Why can a window function not appear in WHERE, and how do you filter on one?
  • What does LAG(x, 1, 0) return on a partition's first row, and on a row whose previous x is NULL?
  • What is the default frame when a window has an ORDER BY, and what does it do with ties?
  • Which frame gives a 3-row moving average, and why must its result be rounded?
  • When does top N per group need RANK rather than ROW_NUMBER?

The three queries are the second-highest salary in every department (ties included), month-over-month revenue growth computed with LAG over a monthly aggregate, and each product's rank and gap to the leader of its category.

Common questions

Why can't I filter on ROW_NUMBER in the WHERE clause?

Window functions are evaluated after WHERE, GROUP BY and HAVING, so WHERE runs before the row numbers exist. Compute the window function in a CTE or derived table and filter on its alias in the outer query.

Which ranking function finds the second highest salary?

DENSE_RANK() OVER (ORDER BY salary DESC), keeping rank 2. Ties at the top share rank 1 and the next distinct salary is 2; with RANK the next salary after a tie could be 3, and with ROW_NUMBER rank 2 could be a second copy of the top salary.

Why does SUM OVER ORDER BY give the same running total on two rows?

The two rows tie on the window's ORDER BY, and the default frame, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, includes all peers of the current row. Add a unique tie-breaker and use a ROWS frame for a per-row total.

AI InterviewGet started

SQL Window Functions Quiz: RANK, LAG and Frames Practice

Test yourself: 12 questions and three queries on OVER and PARTITION BY, row numbers and ranks, LAG and LEAD, frames, running totals and top N per group.

  • Course: SQL study plan
  • Module: Window functions
  • Kind: Checkpoint — cleared at 70%
  • Reading time: 25 min
  • Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — Window functions is the checkpoint that closes the Window functions module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

This checkpoint covers the whole module: window functions with OVER, partitions and the whole-result window OVER (), where windows sit in the order of evaluation, ROW_NUMBER, RANK, DENSE_RANK and NTILE and how they treat ties, LAG, LEAD and FIRST_VALUE, frames with ROWS and RANGE, running totals and moving averages, and the top-N-per-group pattern.

How it works. Twelve questions and three queries. You need 70% on the questions and every query accepted to clear the module. You can retake it as often as you like; your best score counts.

Before you start, make sure you can answer these from memory:

  • What is the difference between GROUP BY dept and PARTITION BY dept?
  • For scores 90, 90, 80, what do ROW_NUMBER, RANK and DENSE_RANK give?
  • Why can a window function not appear in WHERE, and how do you filter on one?
  • What does LAG(x, 1, 0) return on a partition's first row, and on a row whose previous x is NULL?
  • What is the default frame when a window has an ORDER BY, and what does it do with ties?
  • Which frame gives a 3-row moving average, and why must its result be rounded?
  • When does top N per group need RANK rather than ROW_NUMBER?

The three queries are the second-highest salary in every department (ties included), month-over-month revenue growth computed with LAG over a monthly aggregate, and each product's rank and gap to the leader of its category.

Common questions

Why can't I filter on ROW_NUMBER in the WHERE clause?

Window functions are evaluated after WHERE, GROUP BY and HAVING, so WHERE runs before the row numbers exist. Compute the window function in a CTE or derived table and filter on its alias in the outer query.

Which ranking function finds the second highest salary?

DENSE_RANK() OVER (ORDER BY salary DESC), keeping rank 2. Ties at the top share rank 1 and the next distinct salary is 2; with RANK the next salary after a tie could be 3, and with ROW_NUMBER rank 2 could be a second copy of the top salary.

Why does SUM OVER ORDER BY give the same running total on two rows?

The two rows tie on the window's ORDER BY, and the default frame, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, includes all peers of the current row. Add a unique tie-breaker and use a ROWS frame for a per-row total.

Exercises

The second-highest salary in every department

For each department, list the employees paid the department's second-highest distinct salary. Departments with only one distinct salary do not appear.

employees: idnamedeptsalary
1AshaEng90000
2FarahEng90000
3BenEng70000
4GitaEng70000
5IvanEng60000
6ChenSales50000
7DevSales40000
8EvaHR45000
9KaiOps30000
10LinOps30000

Return dept, name and salary. Order by dept, then name.

dept | name | salary
Eng | Ben | 70000
Eng | Gita | 70000
Sales | Dev | 40000

Month-over-month growth

Report revenue per month and its growth over the previous month that had orders.

orders: idorder_dateamount
12024-01-04100
22024-01-1950
32024-02-02300
42024-03-11200
52024-03-3040
62024-04-15240

Return month (DATE_FORMAT(order_date, '%Y-%m')), revenue (the month's sum), prev_revenue (the previous listed month's revenue, NULL for the first) and growth_pct (100 * (revenue - prev_revenue) / prev_revenue, ROUNDed to 1 place). Order by month.

month | revenue | prev_revenue | growth_pct
2024-01 | 150 | NULL | NULL
2024-02 | 300 | 150 | 100
2024-03 | 240 | 300 | -20
2024-04 | 240 | 240 | 0

Rank and gap to the category leader

Rank each product by price within its category (most expensive first, ties sharing a rank and leaving a gap after), and show how far below the category's most expensive product it is.

products: idcategorynameprice
1AudioHeadphones4999
2AudioSpeaker2999
3AudioEarbuds2999
4AudioCable299
5VideoTV39999
6VideoProjector25999
7GamingConsole49999

Return category, name, price, price_rank and gap_to_top (the category's highest price minus this price). Order by category, price_rank, then name.

category | name | price | price_rank | gap_to_top
Audio | Headphones | 4999 | 1 | 0
Audio | Earbuds | 2999 | 2 | 2000
Audio | Speaker | 2999 | 2 | 2000
Audio | Cable | 299 | 4 | 4700
Gaming | Console | 49999 | 1 | 0
Video | TV | 39999 | 1 | 0
Video | Projector | 25999 | 2 | 14000

In this module: Window functions

← Top N per group — the most asked window-function question · INSERT — adding rows →