SQL Window Functions Fundamentals Quiz Quiz

Test your understanding of SQL window functions, their syntax, usage, and key concepts with this beginner-friendly quiz. You'll cover essentials of OVER(), PARTITION BY, RANK, ROW_NUMBER, and practical examples to boost your SQL analytics skills.

1. Definition of SQL Window Function

   Which statement best describes a window function in SQL?

   1. A window function always summarizes data into a single row.
   2. A window function calculates values across a set of related rows while retaining all original rows.
   3. A window function is another name for a trigger.
   4. A window function can only be used inside a WHERE clause.

   Explanation: A window function performs calculations across a window of rows related to each row, but it doesn't collapse the result, keeping the original rows visible. Summarizing data into a single row is done by aggregate functions, not window functions. Window functions cannot be used in the WHERE clause and are unrelated to triggers.

2. Purpose of OVER() Clause

   What is the main role of the OVER() clause in an SQL window function?

   1. It defines which rows the window function operates on.
   2. It is used to rename columns in the query.
   3. It joins two tables based on a condition.
   4. It restricts results similar to a WHERE clause.

   Explanation: The OVER() clause specifies the partition and order of rows for the window function. It does not rename columns (that is done by AS), filter results (WHERE does that), or create joins (which use JOIN clauses).

3. Difference From GROUP BY

   How do window functions differ from using the GROUP BY clause in SQL?

   1. Window functions retain all original rows and add calculated columns, while GROUP BY collapses rows into summaries.
   2. GROUP BY is only used in SELECT statements, not window functions.
   3. Window functions remove duplicate rows, while GROUP BY keeps them.
   4. Window functions can only be used with numeric data, whereas GROUP BY works for all data types.

   Explanation: Window functions enable calculations over sets of rows related to the current row without eliminating any rows, while GROUP BY aggregates and returns a single row per group. Removing duplicates is done by DISTINCT, and GROUP BY is often used in SELECT too. Both work with various data types.

4. Using Window Functions Without PARTITION BY

   What happens if you use a window function without a PARTITION BY clause?

   1. Results are only grouped by primary key.
   2. The function operates over the entire result set as a single group.
   3. It produces an error and the query fails.
   4. It applies the function to each column instead of the entire row.

   Explanation: Without PARTITION BY, window functions treat all rows as a single partition, calculating results across the entire set. This doesn't generate errors, nor does it force grouping by primary keys or columns.

5. Window Functions in WHERE Clause

   Can you directly use a window function in the WHERE clause of a SQL statement?

   1. No, because window functions replace the SELECT statement.
   2. Yes, but only with SUM() window functions.
   3. Yes, window functions can be used in WHERE just like any aggregate function.
   4. No, because window functions are computed after the WHERE clause.

   Explanation: Window functions are evaluated after WHERE, so their results aren't available there. You must use subqueries or common table expressions. SUM() as a window function is not an exception, and window functions do not replace SELECT.

6. Calculating Running Totals

   Which SQL window function pattern is commonly used to calculate running totals within each product category?

   1. AVG(sales) GROUP BY category
   2. SUM(sales) OVER (ORDER BY category)
   3. COUNT(*) OVER (ORDER BY sales)
   4. SUM(sales) OVER (PARTITION BY category ORDER BY product_id)

   Explanation: This sums sales for each category up to the current row, creating a running total. The AVG(sales) GROUP BY version summarizes by group, not as a running total. COUNT(*) counts rows, not sums, and ordering only by category does not yield correct running totals per category.

7. Ranking Employees by Department

   Which function is suitable to rank employees by their salary within each department?

   1. RANK() GROUP BY department_id
   2. RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
   3. ROW_COUNT() OVER (ORDER BY salary)
   4. SUM(salary) OVER (PARTITION BY department_id)

   Explanation: RANK() with PARTITION BY and ORDER BY assigns rankings per department by salary. SUM() produces totals, ROW_COUNT() is not a valid function, and RANK() with GROUP BY does not work in SQL.

8. ROW_NUMBER vs. RANK vs. DENSE_RANK

   How does ROW_NUMBER() differ from RANK() and DENSE_RANK()?

   1. ROW_NUMBER() assigns unique numbers to each row, RANK() leaves gaps for ties, and DENSE_RANK() does not leave gaps.
   2. All three functions always produce identical results.
   3. ROW_NUMBER() and RANK() are the same, DENSE_RANK() is different.
   4. RANK() assigns random numbers, ROW_NUMBER() assigns by alphabetical order.

   Explanation: ROW_NUMBER() gives unique sequential numbers. RANK() assigns the same rank to ties but skips subsequent ranks, and DENSE_RANK() does not skip ranks. They are not identical, and RANK() does not assign random numbers.

9. Finding Top 3 Salaries in Each Department

   Which query structure helps select the top 3 highest-paid employees in each department with window functions?

   1. Select employees WHERE salary u003E 3.
   2. Apply DISTINCT on salary column.
   3. Use MAX(salary) GROUP BY department_id.
   4. Use RANK() OVER (PARTITION BY department_id ORDER BY salary DESC), then filter ranks less than or equal to 3 in a subquery.

   Explanation: RANK() with a subquery or common table expression enables ranking and subsequent filtering. MAX() only finds the highest. DISTINCT doesn't limit rows to the top 3 per group, and WHERE salary u003E 3 just filters by salary value.

10. ROLE OF ORDER BY in Window Functions

    What is the result of including an ORDER BY clause inside OVER() in a window function?

    1. It is ignored by the server.
    2. It defines the order in which the function processes rows, such as for running totals or ranking.
    3. It determines the order of displayed results only.
    4. It prevents the window function from working on groups.

    Explanation: ORDER BY inside OVER() specifies how rows are processed for calculation, influencing results like running totals or ranks. It doesn't merely arrange output or block group operations. Ignoring ORDER BY would be incorrect.

11. Identifying Window Functions

    Which of the following is a standard SQL window function?

    1. CONCAT()
    2. START_WITH()
    3. LEFT()
    4. ROW_NUMBER()

    Explanation: ROW_NUMBER() is a core window function for numbering rows. LEFT() and CONCAT() are string functions and START_WITH() is not standard SQL. Only ROW_NUMBER() can be used as a window function.

12. Use Case for PARTITION BY

    Why would you use the PARTITION BY clause within a window function?

    1. To filter out duplicate rows.
    2. To divide results by certain columns and apply the function to each partition.
    3. To join multiple tables.
    4. To alphabetically sort the final results.

    Explanation: PARTITION BY splits data into groups for the window function. It does not sort, filter duplicates, or join tables—those are handled by other SQL features.

13. Finding the Maximum Value in Each Group

    How would you write a query to display the maximum score in each class, showing all students and their scores?

    1. SELECT MAX(score) FROM students GROUP BY class;
    2. SELECT student_id, class, score, MAX(score) OVER (ORDER BY score) AS max_score FROM students;
    3. SELECT student_id, class, score, MAX(score) OVER (PARTITION BY class) AS max_score FROM students;
    4. SELECT student_id, MAX(score) FROM students WHERE class=class;

    Explanation: Using MAX() as a window function over PARTITION BY class returns each student's data with the class maximum. GROUP BY collapses results and shows only the max per class, omitting other details. The third option is incorrect syntax, and the last option doesn't partition by class.

14. ORDER BY vs. PARTITION BY

    Which clause in a window function controls the order rows are evaluated in, while the other splits rows into groups?

    1. PARTITION BY controls order, ORDER BY splits into groups.
    2. Both clauses always do the same thing.
    3. ORDER BY controls order, PARTITION BY splits into groups.
    4. Neither clause affects window function results.

    Explanation: ORDER BY inside OVER() controls the order of evaluation, affecting calculations. PARTITION BY breaks data into groups for separate calculations. They serve distinct purposes and never swap roles.

15. Default Behavior Without ORDER BY

    If you omit ORDER BY inside the OVER() clause for a window function like SUM(), what is the effect?

    1. The function randomly selects order for calculation.
    2. The function calculates a total for each row's partition, identical across all in the partition.
    3. The function outputs a running total for each row.
    4. The query returns an error and will not execute.

    Explanation: Without ORDER BY, the window function treats the entire partition as the window for each row, so every row in a partition gets the same value. It does not error or create running totals, and SQL never calculates using a random order.

16. Syntax of Basic Window Function

    What is the minimum syntax required to use a window function in SQL?

    1. HAVING FUNCTION_NAME()
    2. OVER FUNCTION_NAME()
    3. FUNCTION_NAME[]
    4. FUNCTION_NAME() OVER ()

    Explanation: The standard syntax for a window function is FUNCTION_NAME() OVER (). The other options are either invalid syntax or do not define a window function.

17. Practical Window Function Calculation Scenario

    If you want to calculate each row's share of total sales, which window function pattern should you use?

    1. SUM(sales) OVER () and divide sales by this sum
    2. SUM(sales) GROUP BY category
    3. COUNT(sales) WHERE sales u003E 0
    4. AVG(sales) OVER (ORDER BY date ROWS 1 PRECEDING)

    Explanation: This pattern gives the grand total in each row, so you can divide individual sales by the total for a percentage. COUNT(sales) is for counts, GROUP BY collapses rows, and AVG with ROWS 1 PRECEDING calculates a small running average, not a ratio.