QuizzesIntermediate SQL Mastery: Window Functions Deep Dive
intermediate
databases

Intermediate SQL Mastery: Window Functions Deep Dive

Test your understanding of advanced SQL window functions in backend engineering.

18 minutes 12 questions Pass at 70%
Time Limit
18 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

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. 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. 2.Which SQL clause is used to define the partitioning of data in a window function?

    • APARTITION BY
    • BORDER BY
    • CGROUP BY
    • DHAVING
  3. 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. 4.Which of the following window functions calculates the cumulative sum of a column?

    • ACUME_DIST()
    • BSUM()
    • CROW_NUMBER()
    • DRANK()
  5. 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. 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. 7.Which window function would you use to calculate the difference between the current row and the previous row?

    • ARANK()
    • BLEAD()
    • CLAG()
    • DNTILE()
  8. 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. 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. 10.Which clause is necessary to use window functions in SQL?

    • AWITH
    • BWHERE
    • COVER
    • DWINDOW
  11. 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. 12.Which window function would you use to find the percentage rank of a row within its partition?

    • APERCENT_RANK()
    • BNTILE()
    • CROW_NUMBER()
    • DRANK()