QuizzesAdvanced SQL Window Functions Mastery for Data Analysis
advanced
databases

Advanced SQL Window Functions Mastery for Data Analysis

Test your expertise in using advanced SQL window functions for complex data analysis tasks.

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 Window Functions Mastery for Data Analysis. 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 time?

    • ANTILE(4) OVER (ORDER BY date)
    • BROW_NUMBER()
    • CSUM() OVER (ORDER BY date)
    • DRANK() OVER (PARTITION BY date)
  2. 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. 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. 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. 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. 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. 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. 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. 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. 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)