SQL Window Functions Interview Quiz Quiz

Test your understanding of SQL window functions with these essential interview questions. This quiz covers the basics, practical applications, and key concepts for mastering window functions in SQL.

1. Definition and Distinction

   Which best describes a window function in SQL, as compared to aggregate functions like SUM or AVG?

   1. A window function creates temporary tables, unlike aggregate functions.
   2. A window function adds a computed column to each row without collapsing rows, while aggregate functions combine rows into a single result.
   3. A window function returns only one row per group, just like aggregate functions.
   4. A window function deletes duplicate rows, while aggregate functions group rows.

2. OVER() Clause Purpose

   What is the primary purpose of the OVER() clause in a SQL window function?

   1. It tells SQL to group rows and return a single result.
   2. It defines which rows are included in the calculation, using options like PARTITION BY and ORDER BY.
   3. It sorts the result set before selecting columns.
   4. It filters results after aggregate functions are calculated.

3. GROUP BY vs Window Functions

   How is a result using GROUP BY different from the same query using a window function with PARTITION BY?

   1. GROUP BY collapses rows into one per group, while window functions keep all original rows and add additional columns.
   2. GROUP BY filters nulls, but window functions ignore them.
   3. GROUP BY always requires a subquery, but window functions do not.
   4. There is no difference; both produce identical results.

4. Omitting PARTITION BY

   If you use a window function with OVER() and do not specify PARTITION BY, what happens?

   1. The function groups by the primary key automatically.
   2. SQL throws an error and stops execution.
   3. The function returns only the first row.
   4. The function is applied to the entire result set as a single group.

5. WHERE Clause Compatibility

   Can window functions be directly used in the WHERE clause of a SELECT statement?

   1. Yes, window functions can be freely used in WHERE clauses.
   2. No, because window functions are computed after the WHERE clause and should be used in a subquery or CTE for filtering.
   3. Yes, but only with the PARTITION BY argument.
   4. Only SUM() can be used in the WHERE clause as a window function.

6. Running Total Example

   Which SQL snippet correctly calculates a running total of sales for each product category ordered by product ID?

   1. SUM(sales) OVER (ORDER BY category, product_id) AS running_total
   2. SUM(sales) OVER (PARTITION BY category ORDER BY product_id) AS running_total
   3. SUM(sales) GROUP BY category, product_id AS running_total
   4. SUM(sales) WINDOW(category) AS running_total

7. Ranking Within a Group

   How can you assign rankings to employees by salary within each department, with the highest salary getting rank 1?

   1. RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
   2. MIN(salary) OVER (PARTITION BY department_id) AS rank
   3. ROW_NUMBER() OVER (PARTITIONED BY salary ASC) AS rank
   4. COUNT() OVER (ORDER salary DESC) AS rank

8. Ranking Functions Compared

   Which statement is TRUE about the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() in SQL?

   1. ROW_NUMBER() gives each row a unique number, RANK() skips numbers after ties, DENSE_RANK() does not skip numbers after ties.
   2. ROW_NUMBER() and RANK() always assign the same value if there are no ties.
   3. DENSE_RANK() randomly skips ranks, while RANK() does not.
   4. ROW_NUMBER() and DENSE_RANK() provide the same output regardless of data.

9. Top-N Records Per Group

   How would you retrieve the top 3 earners in each department using SQL window functions?

   1. Select the first three salaries in each department without any window function.
   2. Use MAX() OVER (PARTITION BY department_id) and filter where salary IN (top 3).
   3. GROUP BY department_id HAVING COUNT(salary) u003E= 3.
   4. Use RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) and filter rows where rank u003C= 3.

10. Moving Average Calculation

    Which SQL code will return a 7-day moving average of sales, including the current and previous six days?

    1. AVG(sales) OVER (PARTITION 7 BY ROW) AS moving_avg
    2. SUM(sales) OVER (ORDER BY 6 PRECEDING sale_date) AS moving_avg
    3. AVG(sales) GROUP BY 7 DAYS AS moving_avg
    4. AVG(sales) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg