Mastering Advanced SQL Window Functions
An advanced quiz to test your understanding of complex SQL window functions.
Questions in this quiz
10 questions on Mastering Advanced SQL Window Functions. Work through them here, then start the quiz to answer them and see your score.
1.Which SQL window function would you use to calculate a running total of sales in a dataset?
- ADENSE_RANK() OVER()
- BROW_NUMBER()
- CSUM() OVER()
- DRANK() OVER()
2.What is the primary difference between ROW_NUMBER() and RANK() window functions?
- ABoth assign the same numbers to ties
- BRANK() assigns unique numbers, ROW_NUMBER() can assign the same number to ties
- CBoth assign unique numbers but in different orders
- DROW_NUMBER() assigns unique numbers, RANK() can assign the same rank to ties
3.In SQL, which window function would you use to find the difference between the current row and the previous row?
- ALEAD()
- BLAST_VALUE()
- CLAG()
- DFIRST_VALUE()
4.Which clause is used to define the partitioning of data in a window function?
- AORDER BY
- BFILTER BY
- CPARTITION BY
- DGROUP BY
5.How does the NTILE(n) window function distribute rows?
- AInto n groups with decreasing size
- BEqually into n groups
- CInto n groups with increasing size
- DRandomly into n groups
6.What is the purpose of the FIRST_VALUE() window function?
- ATo return the first value in the entire table
- BTo return the first row in the result set
- CTo return the first value in a partition
- DTo return the first distinct value
7.Which window function would you use to calculate the average salary within each department?
- ACOUNT() OVER(PARTITION BY department)
- BAVG() OVER(PARTITION BY department)
- CSUM() OVER(PARTITION BY department)
- DMAX() OVER(PARTITION BY department)
8.What does the LEAD() function do in SQL?
- AAccesses data from the last row
- BAccesses data from the first row
- CAccesses data from the next row
- DAccesses data from the previous row
9.Which of the following window functions can be used to assign a unique sequential integer to rows within a partition?
- ADENSE_RANK()
- BNTILE()
- CROW_NUMBER()
- DRANK()
10.How does the DENSE_RANK() function differ from RANK() in handling ties?
- ANeither skips ranks after ties
- BRANK() skips ranks after ties
- CDENSE_RANK() skips ranks after ties
- DBoth skip ranks after ties