Advanced SQL: Mastering Window Functions
Explore the intricacies of SQL window functions and their practical applications.
Questions in this quiz
10 questions on Advanced SQL: Mastering 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 over a partitioned dataset?
- ANTILE() OVER()
- BROW_NUMBER() OVER()
- CRANK() OVER()
- DSUM() OVER()
2.What is the primary difference between ROW_NUMBER() and RANK() window functions?
- ARANK() assigns unique numbers, ROW_NUMBER() can assign the same rank to ties
- BROW_NUMBER() assigns unique numbers, RANK() can assign the same rank to ties
- CBoth can assign the same rank to ties
- DBoth assign unique numbers
3.In which scenario would you use the LAG() window function?
- ATo calculate cumulative sums
- BTo assign a unique rank to each row
- CTo divide rows into a specified number of groups
- DTo calculate the difference between current and previous row values
4.How does the NTILE() function distribute rows in a partition?
- ABased on the alphabetical order of a column
- BRandomly into buckets
- CEqually into a specified number of buckets
- DBy assigning a unique rank to each row
5.Which window function would you use to find the highest salary in each department?
- AAVG() OVER(PARTITION BY department)
- BMAX() OVER(PARTITION BY department)
- CSUM() OVER(PARTITION BY department)
- DMIN() OVER(PARTITION BY department)
6.What is the purpose of the PARTITION BY clause in window functions?
- ATo filter rows before applying window functions
- BTo limit the number of rows processed
- CTo define how to group rows for window calculations
- DTo sort the entire dataset
7.Which function would you use to assign a unique sequential integer to each row within a partition?
- ARANK() OVER()
- BDENSE_RANK() OVER()
- CNTILE() OVER()
- DROW_NUMBER() OVER()
8.What is the difference between DENSE_RANK() and RANK() functions?
- ARANK() does not skip ranks after ties, DENSE_RANK() does
- BBoth skip ranks after ties
- CBoth do not skip ranks after ties
- DDENSE_RANK() does not skip ranks after ties, RANK() does
9.Which window function would you use to calculate a moving average?
- AAVG() OVER(ORDER BY column ROWS BETWEEN x PRECEDING AND CURRENT ROW)
- BSUM() OVER()
- CROW_NUMBER() OVER()
- DCOUNT() OVER()
10.How can you use the LEAD() function in SQL?
- ATo calculate the cumulative sum
- BTo access data from a subsequent row in the same result set
- CTo rank rows within a partition
- DTo assign a unique number to each row