Advanced SQL Window Functions Mastery for Data Analysis
Test your expertise in using advanced SQL window functions for complex data analysis tasks.
Questions in this quiz
10 questions on Advanced SQL Window Functions Mastery for Data Analysis. 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 time?
- ANTILE(4) OVER (ORDER BY date)
- BROW_NUMBER()
- CSUM() OVER (ORDER BY date)
- DRANK() OVER (PARTITION BY date)
2.What is the primary difference between ROW_NUMBER() and RANK() window functions?
- AROW_NUMBER() skips numbers for ties, RANK() does not
- BROW_NUMBER() and RANK() are identical
- CRANK() is faster than ROW_NUMBER()
- DRANK() skips numbers for ties, ROW_NUMBER() does not
3.In a SQL query, how would you calculate the average salary of employees within each department using window functions?
- AMIN(salary) OVER (PARTITION BY department_id)
- BCOUNT(salary) OVER (PARTITION BY department_id)
- CAVG(salary) OVER (PARTITION BY department_id)
- DSUM(salary) OVER (PARTITION BY department_id)
4.Which window function would you use to assign a unique rank to each row, without gaps, even if there are ties?
- ARANK()
- BROW_NUMBER()
- CDENSE_RANK()
- DNTILE()
5.How can you use the LAG() function to compare each row's value with the previous row's value in a dataset?
- ALAG(value) OVER (ORDER BY id)
- BRANK() OVER (ORDER BY id)
- CROW_NUMBER() OVER (ORDER BY id)
- DLEAD(value) OVER (ORDER BY id)
6.What is the purpose of the NTILE() window function in SQL?
- ATo assign a unique number to each row
- BTo calculate the moving average
- CTo calculate the cumulative distribution
- DTo divide rows into a specified number of buckets
7.Which window function would you use to find the cumulative distribution of a value in a dataset?
- ACUME_DIST() OVER (ORDER BY value)
- BNTILE(100) OVER (ORDER BY value)
- CDENSE_RANK() OVER (ORDER BY value)
- DPERCENT_RANK() OVER (ORDER BY value)
8.In SQL, how can you calculate the difference between the current row's value and the next row's value?
- ALAG(value) OVER (ORDER BY id) - value
- BROW_NUMBER() OVER (ORDER BY id) - value
- CLEAD(value) OVER (ORDER BY id) - value
- DRANK() OVER (ORDER BY id) - value
9.What does the PARTITION BY clause do in a window function?
- AFilters the result set
- BOrders the result set
- CDivides the result set into partitions
- DGroups the result set
10.Which window function would you use to calculate a moving average over a specified number of preceding rows?
- ASUM(value) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
- BMIN(value) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
- CAVG(value) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
- DCOUNT(value) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)