Practice vocabulary for SQL window functions including OVER, PARTITION BY, ROW_NUMBER, RANK, LAG/LEAD, and running totals.
0 / 18 completed
1 / 18
The clause that defines the frame of rows a window function operates over is the _____ clause.
OVER (...) is the mandatory clause that turns an aggregate or ranking function into a window function, defining the partition and ordering of the window.
2 / 18
You want to rank rows within each department independently. Which clause inside OVER achieves this?
PARTITION BY resets the window function's calculation for each distinct value of the partition column — similar to GROUP BY but without collapsing rows.
3 / 18
What is the difference between RANK() and DENSE_RANK() when there are ties?
RANK leaves gaps after ties (e.g., 1, 2, 2, 4), while DENSE_RANK assigns consecutive integers without gaps (e.g., 1, 2, 2, 3).
4 / 18
Which window function pair allows you to access the value from the previous or next row without a self-join?
LAG accesses the value from a preceding row and LEAD accesses a following row within the defined window, eliminating the need for a self-join.
5 / 18
A window function with SUM(...) OVER (ORDER BY date) computes a _____.
SUM with an ORDER BY inside OVER creates a running total (cumulative sum) — each row's value is the sum of all rows up to and including the current one.
6 / 18
Code Review Comment
During a code review of a query to calculate the moving average sales for each product category over the last 7 days, Sarah noticed that David used the `AVG()` window function without specifying an `ORDER BY` clause. She commented: 'David, this moving average calculation isn't quite right – you're averaging all sales data, not rolling it back 7 days! Consider using a frame specification to control how the window functions over time.' Which of the following options best describes what Sarah was referring to?
— the account balance is too low...
Sarah is referring to the `OVER (ORDER BY ...)` clause. This is crucial for window functions because it defines the *frame* – the set of rows used in calculating the result. Without an `ORDER BY`, the default frame behavior is often unpredictable and incorrect when dealing with time-series data or any calculation requiring a specific sequence, like a moving average. The other options relate to different SQL concepts (partitioning, filtering, or aggregation) that are not directly involved in defining the window function's frame.
7 / 18
During a code review of a query to calculate the running total of website visits for each user over the past month, Mark observed that Emily's query was using `SUM()` with an `OVER` clause but without any frame specification. He explained: 'Emily, you need to define how many rows are included in this sum – are you looking at the *current* visit or all visits within the last month? Without a frame, it's just summing up *all* visits.' Which of the following best describes Mark's concern?
The core issue here isn't about filtering by user ID or using `ROW_NUMBER()`. Instead, Mark is highlighting the critical role of the frame specification in window functions. The frame defines the *window* – the set of rows – over which the aggregate function (in this case, `SUM()`) operates. Without a frame, the `SUM()` function calculates the sum of *all* rows in the result set, ignoring any temporal constraints; Emily needed to specify if she wanted the running total based on the current visit or all visits within the last month.
8 / 18
Code Review Comment
During a code review of a query to calculate the moving average sales for each product category over the last 7 days, Sarah noticed that David used the `AVG()` window function without specifying an `ORDER BY` clause. She commented: 'David, this moving average calculation isn't quite right – you're averaging all sales data, not rolling it back 7 days! Consider using a frame specification to control how the window functions over time.' Which of the following options best describes what Sarah was referring to?
— the account balance is too low...
Sarah is referring to the `OVER (ORDER BY ...)` clause. This is crucial for window functions because it defines the *frame* – the set of rows used in calculating the result. Without an `ORDER BY`, the default frame behavior is often unpredictable and incorrect when dealing with time-series data or any calculation requiring a specific sequence, like a moving average. The other options relate to different SQL concepts (partitioning, filtering, or aggregation) that are not directly involved in defining the window function's frame.
9 / 18
During a code review of a query to calculate the running total of website visits for each user over the past month, Mark observed that Emily's query was using `SUM()` with an `OVER` clause but without any frame specification. He explained: 'Emily, you need to define how many rows are included in this sum – are you looking at the *current* visit or all visits within the last month? Without a frame, it's just summing up *all* visits.' Which of the following best describes Mark's concern?
The core issue here isn't about filtering by user ID or using `ROW_NUMBER()`. Instead, Mark is highlighting the critical role of the frame specification in window functions. The frame defines the *window* – the set of rows – over which the aggregate function (in this case, `SUM()`) operates. Without a frame, the `SUM()` function calculates the sum of *all* rows in the result set, ignoring any temporal constraints; Emily needed to specify if she wanted the running total based on the current visit or all visits within the last month.
10 / 18
Code Review Comment
During a code review of a query to calculate the moving average sales for each product category over the last 7 days, Sarah noticed that David used the `AVG()` window function without specifying an `ORDER BY` clause. She commented: 'David, this moving average calculation isn't quite right – you're averaging all sales data, not rolling it back 7 days! Consider using a frame specification to control how the window functions over time.' Which of the following options best describes what Sarah was referring to?
— the account balance is too low...
Sarah is referring to the `OVER (ORDER BY ...)` clause. This is crucial for window functions because it defines the *frame* – the set of rows used in calculating the result. Without an `ORDER BY`, the default frame behavior is often unpredictable and incorrect when dealing with time-series data or any calculation requiring a specific sequence, like a moving average. The other options relate to different SQL concepts (partitioning, filtering, or aggregation) that are not directly involved in defining the window function's frame.
11 / 18
During a code review of a query to calculate the running total of website visits for each user over the past month, Mark observed that Emily's query was using `SUM()` with an `OVER` clause but without any frame specification. He explained: 'Emily, you need to define how many rows are included in this sum – are you looking at the *current* visit or all visits within the last month? Without a frame, it's just summing up *all* visits.' Which of the following best describes Mark's concern?
The core issue here isn't about filtering by user ID or using `ROW_NUMBER()`. Instead, Mark is highlighting the critical role of the frame specification in window functions. The frame defines the *window* – the set of rows – over which the aggregate function (in this case, `SUM()`) operates. Without a frame, the `SUM()` function calculates the sum of *all* rows in the result set, ignoring any temporal constraints; Emily needed to specify if she wanted the running total based on the current visit or all visits within the last month.
12 / 18
Code Review Comment
During a code review of a query to calculate the moving average sales for each product category over the last 7 days, Sarah noticed that David used the `AVG()` window function without specifying an `ORDER BY` clause. She commented: 'David, this moving average calculation isn't quite right – you're averaging all sales data, not rolling it back 7 days! Consider using a frame specification to control how the window functions over time.' Which of the following options best describes what Sarah was referring to?
— the account balance is too low...
Sarah is referring to the `OVER (ORDER BY ...)` clause. This is crucial for window functions because it defines the *frame* – the set of rows used in calculating the result. Without an `ORDER BY`, the default frame behavior is often unpredictable and incorrect when dealing with time-series data or any calculation requiring a specific sequence, like a moving average. The other options relate to different SQL concepts (partitioning, filtering, or aggregation) that are not directly involved in defining the window function's frame.
13 / 18
During a code review of a query to calculate the running total of website visits for each user over the past month, Mark observed that Emily's query was using `SUM()` with an `OVER` clause but without any frame specification. He explained: 'Emily, you need to define how many rows are included in this sum – are you looking at the *current* visit or all visits within the last month? Without a frame, it's just summing up *all* visits.' Which of the following best describes Mark's concern?
The core issue here isn't about filtering by user ID or using `ROW_NUMBER()`. Instead, Mark is highlighting the critical role of the frame specification in window functions. The frame defines the *window* – the set of rows – over which the aggregate function (in this case, `SUM()`) operates. Without a frame, the `SUM()` function calculates the sum of *all* rows in the result set, ignoring any temporal constraints; Emily needed to specify if she wanted the running total based on the current visit or all visits within the last month.
14 / 18
During a Slack conversation with the team about optimizing query performance, Alex mentions that he's using a window function to calculate cumulative sales totals. He says, 'I'm using SUM() OVER (ORDER BY sale_date) but I'm not sure if I need to explicitly define the frame. What does the 'frame' clause in this context refer to?'
The 'frame' clause within an OVER() clause specifies the set of rows that contribute to the calculation for each row. In this case, it defines how many previous sales (ordered by sale_date) are included in the cumulative total. A common misconception is that it's about database systems; it's a fundamental SQL concept regardless of the specific engine.
15 / 18
You are reviewing a PR containing the following SQL query:
SELECT order_id, customer_id, total_amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS rank FROM orders
What is the primary purpose of using ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) in this query?
The ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) function assigns a rank to each order within its respective customer group. 'PARTITION BY' creates the groups (customer_id), and 'ORDER BY' determines how the ranking is done *within* that group—highest total amount gets rank 1. This allows you to identify top-spending customers.
16 / 18
During a standup meeting, Ben explains he's using window functions to calculate the running difference between consecutive daily website visits. He wants to ensure that if multiple users have the same number of visits on a given day, they all receive the same rank within their group. Which window function is best suited for this scenario?
When ties exist in the ordering criteria (same number of visits), DENSE_RANK() assigns consecutive ranks without gaps. This ensures that users with equal visit counts receive adjacent rank values. RANK(), on the other hand, would skip ranks due to ties, which isn't desired for this running difference calculation.
17 / 18
You're tasked with creating a report showing the percentage of sales exceeding a target threshold for each product category. The data is stored in a table named `sales_data` with columns `product_category`, `sale_amount`, and `target_amount`. Which window function combination would be most appropriate to achieve this?
The query needs to count the number of sales that exceed the target for each category. SUM(CASE WHEN sale_amount > target_amount THEN 1 ELSE 0 END) OVER (PARTITION BY product_category) calculates this precisely by summing up the values where the condition is true (sale amount greater than target). The `CASE` statement acts as a conditional expression within the aggregate function.
18 / 18
A developer submits a PR with this SQL query:
SELECT item_name, price, AVG(price) OVER (ORDER BY price DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_average_price FROM products
What does the ROWS BETWEEN 1 PRECEDING AND CURRENT ROW clause specify in this window function?
This clause defines the frame of the window function. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW means that for each row, the moving average is calculated using the current row's price and the price from one preceding row in the ordered sequence. This creates a rolling or sliding window.
What does the "SQL Window Functions Vocabulary" exercise practise?
Practice vocabulary for SQL window functions including OVER, PARTITION BY, ROW_NUMBER, RANK, LAG/LEAD, and running totals.
How many questions are in this exercise?
This exercise has 18 questions, each multiple-choice with a full explanation shown after you answer.
What English level is this exercise for?
This exercise is tagged Intermediate. If the vocabulary feels difficult, browse the Database & SQL category page for an easier module to start with.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free with no account, sign-up, or paywall.
Do I get feedback if I answer incorrectly?
Yes — whichever option you choose, right or wrong, you'll immediately see an explanation clarifying the correct term and why the other options don't fit.
Can I retry this exercise?
Yes — once you finish all the questions, a "Try again" button on the results screen resets the exercise so you can practise as many times as you like.
Do I need an account to track my progress?
No account is required. Your progress bar and score for this session are tracked in the browser as you go, but nothing is saved once you leave the page.
Is "SQL Window Functions Vocabulary" part of a larger series?
Yes — it's one exercise in the Database & SQL category on CoderSlingo. See the category page for the full list of related exercises on similar terminology.
Can I link directly to this exercise?
Yes — this exercise has its own permanent URL, so you can bookmark it or share the link directly with a colleague or study partner.
Where can I find more exercises like this one?
See the Database & SQL category page for related exercises, or browse the main Exercises hub for other IT English topics.