Intermediate SQL Mastery: Window Functions Deep Dive
Test your understanding of advanced SQL window functions in backend engineering.
Questions in this quiz
12 questions on Intermediate SQL Mastery: Window Functions Deep Dive. Work through them here, then start the quiz to answer them and see your score.
1.What is the primary purpose of using window functions in SQL?
- ATo perform calculations across a set of table rows related to the current row.
- BTo create temporary tables for complex queries.
- CTo enforce data integrity constraints.
- DTo optimize query performance by indexing.
2.Which SQL clause is used to define the partitioning of data in a window function?
- APARTITION BY
- BORDER BY
- CGROUP BY
- DHAVING
3.Consider the SQL snippet: `SELECT department, employee, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;`. What does this query achieve?
- ARanks employees within each department by salary.
- BRanks employees globally by salary.
- CLists employees without ranking.
- DCalculates the average salary per department.
4.Which of the following window functions calculates the cumulative sum of a column?
- ACUME_DIST()
- BSUM()
- CROW_NUMBER()
- DRANK()
5.What is the difference between ROW_NUMBER() and RANK() window functions?
- ABoth assign unique numbers to each row.
- BROW_NUMBER() assigns unique numbers, RANK() may assign the same rank to ties.
- CBoth assign the same number to ties.
- DRANK() assigns unique numbers, ROW_NUMBER() may assign the same number to ties.
6.In SQL, what does the LEAD() window function do?
- ADetermines the rank of a row within a partition.
- BReturns the value of a subsequent row in the same result set.
- CCalculates the average of a column.
- DReturns the value of a previous row in the same result set.
7.Which window function would you use to calculate the difference between the current row and the previous row?
- ARANK()
- BLEAD()
- CLAG()
- DNTILE()
8.What does the NTILE(n) window function do?
- APartitions the dataset into n equal parts.
- BDivides the result set into n buckets and assigns a bucket number to each row.
- CReturns the top n rows from a dataset.
- DCalculates the nth percentile of a dataset.
9.How does the DENSE_RANK() function differ from the RANK() function?
- ADENSE_RANK() does not skip ranks after ties.
- BDENSE_RANK() skips ranks after ties.
- CDENSE_RANK() is used only for numeric columns.
- DDENSE_RANK() assigns unique numbers to each row.
10.Which clause is necessary to use window functions in SQL?
- AWITH
- BWHERE
- COVER
- DWINDOW
11.What will the function `FIRST_VALUE(column)` return when used as a window function?
- AThe first value in the current window partition.
- BThe first non-null value in the dataset.
- CThe first value in the entire dataset.
- DThe first value after sorting the dataset.
12.Which window function would you use to find the percentage rank of a row within its partition?
- APERCENT_RANK()
- BNTILE()
- CROW_NUMBER()
- DRANK()