Describe Advanced SQL Methods: Window Functions, Ordered Aggregates, and MADlib DATA-SCIENCE-FOUNDATIONS Practice Questions (Page 3)

Questions 11–15

1. 11foundation · easy

   In the query: SELECT ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees; what does the PARTITION BY department clause do?

   • AIt groups the output into a single row per department.
   • BIt divides the rows into separate groups by department, and the row number is assigned within each group.
   • CIt sorts the entire result set by department.
   • DIt filters out rows that do not belong to the department.
   Reveal Solution Discussion Select an answer first
2. 12application · medium

   An analyst needs to compute the median salary for each department in a PostgreSQL table. The median must be an interpolated value between two middle values when the department has an even number of employees. Which SQL function should they use?

   • AAVG(salary) OVER (PARTITION BY department)
   • Bpercentile_disc(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department)
   • Cpercentile_cont(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department)
   • DROW_NUMBER() OVER (PARTITION BY department ORDER BY salary)
   Reveal Solution Discussion Select an answer first
4. 13expert · hard

   A data analyst needs to identify the top 5 products by revenue per category, but the requirement is that if multiple products tie for 5th place, all of them must be included. The analyst also needs to compute the cumulative revenue share for each product within its category. Which approach should they use?

   • AUse NTILE(5) OVER (PARTITION BY category ORDER BY revenue DESC) and SUM(revenue) OVER (PARTITION BY category ORDER BY revenue DESC)
   • BUse RANK() OVER (PARTITION BY category ORDER BY revenue DESC) to filter rank <= 5, and SUM(revenue) OVER (PARTITION BY category ORDER BY revenue DESC) for cumulative share
   • CUse DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) to filter dense_rank <= 5, and AVG(revenue) OVER (PARTITION BY category) for cumulative share
   • DUse ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) to filter row_number <= 5, and SUM(revenue) OVER (PARTITION BY category ORDER BY revenue DESC) for cumulative share
   Reveal Solution Discussion Select an answer first
5. 14foundation · easy

   Which window function would you use to return the value of a column from the row immediately following the current row within the same partition?

   • ALEAD()
   • BDENSE_RANK()
   • CLAG()
   • DROW_NUMBER()
   Reveal Solution Discussion Select an answer first
6. 15foundation · easy

   What is the primary purpose of a window function in SQL?

   • ATo enforce referential integrity between tables by defining foreign key constraints.
   • BTo filter rows based on a condition that compares values across different tables.
   • CTo perform calculations across a set of rows that are related to the current row, without collapsing them into a single output row.
   • DTo aggregate all rows in a table into a single summary row, similar to a GROUP BY query.
   Reveal Solution Discussion Select an answer first