Window Functions — Concept Check
Questions on Window Functions. ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, SUM OVER PARTITION — window functions compute a result across a sliding frame without collapsing rows like GROUP BY.
Questions in this quiz
8 questions on Window Functions — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.You have a sales table and want to calculate the cumulative sales up to each date. Which SQL clause would you use with SUM to achieve this?
- AGROUP BY sale_date
- BOVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
- COVER (PARTITION BY sale_date)
- DORDER BY sale_date
2.A leaderboard ranks players by score. Two players have the same score. Using RANK(), what happens to the rank of the next player?
- AThe next rank is the same as the tied players
- BThe next rank is incremented by 1
- CThe next rank is skipped
- DThe next rank is incremented by 2
3.You want to find the top 3 sales amounts per region without collapsing rows. Which function should you use?
- AGROUP BY region
- BSUM() OVER (PARTITION BY region)
- CROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC)
- DCOUNT() OVER (PARTITION BY region)
4.In a table of daily sales, how would you calculate the percentage change from the previous day for each row?
- AUsing FIRST_VALUE() to get the first day’s total
- BUsing SUM() to get the cumulative total
- CUsing LAG() to get the previous day’s total and calculating the difference
- DUsing LEAD() to predict the next day’s total
5.A report shows a sudden drop in sales on a specific day. How can you verify if this drop is significant compared to previous days?
- AUse FIRST_VALUE() to compare with the first sale
- BCalculate the day-over-day percentage change using LAG()
- CUse ROW_NUMBER() to list sales
- DUse DENSE_RANK() to rank sales amounts
6.In a dataset of user events, you need to find the time gap between consecutive events for each user. Which function would you use?
- ARANK() to rank the events
- BLAG() to find the previous event time
- CLEAD() to find the next event time
- DFIRST_VALUE() to find the first event time
7.You want to calculate a 7-day moving average of daily sales. Which SQL clause is necessary to define the window frame?
- AORDER BY sale_date DESC
- BGROUP BY sale_date
- CROWS BETWEEN 6 PRECEDING AND CURRENT ROW
- DPARTITION BY sale_date
8.A developer uses ROW_NUMBER() to rank items but notices gaps in the sequence. What might be the cause?
- AUsing DENSE_RANK() instead of ROW_NUMBER()
- BIncorrect use of PARTITION BY
- CUsing RANK() instead of ROW_NUMBER()
- DUsing ORDER BY incorrectly