QuizzesAdvanced SQL: Mastering Window Functions
advanced
databases

Advanced SQL: Mastering Window Functions

Explore the intricacies of SQL window functions and their practical applications.

20 minutes 10 questions Pass at 70%
Time Limit
20 minutes - timer starts when you begin
Change Answers Freely
Revisit and change any answer before submitting
Full Review After Submit
Correct answers and explanations shown for every question
Auto-submit on timeout
Quiz submits automatically when time runs out

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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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