Mastering SQL Window Functions: Intermediate Challenges
Test your knowledge of SQL window functions with these intermediate-level questions.
Questions in this quiz
12 questions on Mastering SQL Window Functions: Intermediate Challenges. Work through them here, then start the quiz to answer them and see your score.
1.What is the primary purpose of the PARTITION BY clause in a window function?
- ATo sort the result set
- BTo limit the number of rows returned
- CTo filter rows in the result set
- DTo divide the result set into groups of rows
2.Which window function would you use to calculate a running total?
- ANTILE()
- BSUM() OVER()
- CROW_NUMBER()
- DRANK()
3.Consider the SQL snippet: SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees. What does this query do?
- AAssigns a unique rank to each employee without ties
- BCalculates the total salary for each employee
- CRanks employees by name in ascending order
- DRanks employees by salary in descending order
4.How does the DENSE_RANK() function differ from RANK()?
- ADENSE_RANK() is faster than RANK()
- BDENSE_RANK() assigns the same rank to all rows
- CDENSE_RANK() does not allow gaps in ranking values
- DDENSE_RANK() only works with numeric columns
5.Which function would you use to assign a unique sequential integer to rows within a partition of a result set?
- AROW_NUMBER()
- BLAG()
- CNTILE()
- DRANK()
6.What does the LAG() function do in SQL?
- AReturns the value of a column from a preceding row
- BCalculates the average of a column
- CReturns the last value in a partition
- DCalculates the difference between two rows
7.In the context of window functions, what does the term 'frame' refer to?
- AA temporary table used for calculations
- BA subset of rows defined by the window function
- CA specific column in the result set
- DThe entire result set
8.Which clause would you use to specify the order of rows processed by a window function?
- AHAVING
- BPARTITION BY
- CGROUP BY
- DORDER BY
9.What is the result of using NTILE(4) in a window function?
- AAssigns a rank to each row
- BCalculates the average of the top four rows
- CDivides the result set into four equal parts
- DReturns the top four rows
10.Which window function would you use to find the highest salary in each department?
- AMIN() OVER(PARTITION BY department)
- BAVG() OVER(PARTITION BY department)
- CMAX() OVER(PARTITION BY department)
- DSUM() OVER(PARTITION BY department)
11.What does the LEAD() function do in SQL?
- ACalculates the lead time between two events
- BAssigns a unique identifier to each row
- CReturns the value of a column from a following row
- DReturns the first value in a partition
12.Which window function would you use to calculate the average salary of the last three employees hired?
- ACOUNT() OVER(ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
- BAVG() OVER(ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
- CSUM() OVER(ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
- DMAX() OVER(ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)