QuizzesMastering SQL Window Functions: Intermediate Challenges
intermediate
databases

Mastering SQL Window Functions: Intermediate Challenges

Test your knowledge of SQL window functions with these intermediate-level questions.

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 Mastering SQL Window Functions: Intermediate Challenges. Work through them here, then start the quiz to answer them and see your score.

  1. 1.What is the primary purpose of the PARTITION BY clause in a window function?

    • ATo sort the result set
    • BTo limit the number of rows returned
    • CTo filter rows in the result set
    • DTo divide the result set into groups of rows
  2. 2.Which window function would you use to calculate a running total?

    • ANTILE()
    • BSUM() OVER()
    • CROW_NUMBER()
    • DRANK()
  3. 3.Consider the SQL snippet: SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees. What does this query do?

    • AAssigns a unique rank to each employee without ties
    • BCalculates the total salary for each employee
    • CRanks employees by name in ascending order
    • DRanks employees by salary in descending order
  4. 4.How does the DENSE_RANK() function differ from RANK()?

    • ADENSE_RANK() is faster than RANK()
    • BDENSE_RANK() assigns the same rank to all rows
    • CDENSE_RANK() does not allow gaps in ranking values
    • DDENSE_RANK() only works with numeric columns
  5. 5.Which function would you use to assign a unique sequential integer to rows within a partition of a result set?

    • AROW_NUMBER()
    • BLAG()
    • CNTILE()
    • DRANK()
  6. 6.What does the LAG() function do in SQL?

    • AReturns the value of a column from a preceding row
    • BCalculates the average of a column
    • CReturns the last value in a partition
    • DCalculates the difference between two rows
  7. 7.In the context of window functions, what does the term 'frame' refer to?

    • AA temporary table used for calculations
    • BA subset of rows defined by the window function
    • CA specific column in the result set
    • DThe entire result set
  8. 8.Which clause would you use to specify the order of rows processed by a window function?

    • AHAVING
    • BPARTITION BY
    • CGROUP BY
    • DORDER BY
  9. 9.What is the result of using NTILE(4) in a window function?

    • AAssigns a rank to each row
    • BCalculates the average of the top four rows
    • CDivides the result set into four equal parts
    • DReturns the top four rows
  10. 10.Which window function would you use to find the highest salary in each department?

    • AMIN() OVER(PARTITION BY department)
    • BAVG() OVER(PARTITION BY department)
    • CMAX() OVER(PARTITION BY department)
    • DSUM() OVER(PARTITION BY department)
  11. 11.What does the LEAD() function do in SQL?

    • ACalculates the lead time between two events
    • BAssigns a unique identifier to each row
    • CReturns the value of a column from a following row
    • DReturns the first value in a partition
  12. 12.Which window function would you use to calculate the average salary of the last three employees hired?

    • ACOUNT() OVER(ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    • BAVG() OVER(ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    • CSUM() OVER(ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    • DMAX() OVER(ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)