SQLite Window Functions and Advanced Query Essentials Quiz

Explore fundamental concepts of window functions and advanced query techniques in SQLite with this beginner-friendly quiz. Understand partitioning, ranking, aggregation, and practical uses of window functions crucial for efficient data analysis.

1. Understanding Window Functions Syntax

   Which keyword is required to define a window function in an SQLite SELECT statement?

   1. WITHIN
   2. OVER
   3. AROUND
   4. USING

   Explanation: The 'OVER' keyword specifies the window for a window function in SQLite, allowing you to perform calculations across a set of rows. 'WITHIN' and 'AROUND' are not recognized keywords in this context, and 'USING' is unrelated to window function declarations. Without 'OVER', the engine cannot apply the window function properly.

2. Aggregate vs. Window Functions

   In the statement SELECT SUM(amount) OVER (), what distinguishes the use of SUM as a window function compared to a regular aggregate?

   1. It removes duplicate values
   2. It sorts rows alphabetically
   3. It returns a value for each row
   4. It ignores NULL values

   Explanation: When used as a window function, SUM(amount) OVER () computes the sum but returns it for every row in the result set. Regular aggregates like SUM without 'OVER' return only one value for the whole group. Ignoring NULLs and sorting rows are not unique to window functions, and removing duplicates is not their primary purpose.

3. Partitioning Data

   Given the query SELECT employee, department, RANK() OVER (PARTITION BY department ORDER BY salary DESC) FROM staff, what does the PARTITION BY clause do?

   1. It groups rows by department before applying the ranking
   2. It sorts results by department
   3. It removes duplicates from departments
   4. It ignores departments with NULL values

   Explanation: The 'PARTITION BY' clause logically groups data by department, so each department gets its own ranking order. It does not remove duplicates, sort results, or ignore NULL values. Those distracted options mix up concepts unrelated to the partitioning mechanism.

4. ROW_NUMBER Function Behavior

   What does the ROW_NUMBER() window function return when applied with ORDER BY transaction_date?

   1. Duplicate numbers if two rows have the same date
   2. The highest transaction_date in the table
   3. The sum of all rows sharing the same transaction_date
   4. A unique sequential number for each row based on transaction_date order

   Explanation: ROW_NUMBER() assigns a distinct number to each row according to the specified order, such as transaction_date. It does not sum values, return the highest date, nor does it assign duplicate numbers when values match. Each row receives a unique identifier regardless of duplicates in the ordered column.

5. Ranking with Ties

   If two salespeople have the same sales figure and RANK() OVER (ORDER BY sales DESC) is used, what rank will they both receive?

   1. One receives no rank
   2. The lower sales figure gets a higher rank
   3. They share the same rank number
   4. They receive consecutive different ranks

   Explanation: RANK() assigns the same rank to tied values, so both salespeople with the same sales figure receive the same rank. They do not get consecutive, different ranks as ROW_NUMBER() would. RANK() does not favor lower values, and it never omits ranking for tied entries.

6. Simple LEAD Function Usage

   What does LEAD(sale, 1) OVER (ORDER BY date) return for each row in a sales table?

   1. The sale value in the next row according to date order
   2. The previous sale value
   3. The total sales up to the current date
   4. The highest sale value in the table

   Explanation: LEAD provides access to subsequent row values, hence it retrieves the sale value from the next row based on the specified order. The previous value would need LAG, not LEAD. It does not find the highest sale or aggregate totals, which are unrelated behaviors.

7. ORDER BY in Window Functions

   Why is the ORDER BY clause important inside window function definitions in SQLite?

   1. It increases the speed of queries
   2. It determines the sequence in which calculations like ranking or running totals are applied
   3. It merges duplicate records
   4. It filters out unwanted rows

   Explanation: ORDER BY within a window function specifies the exact order for evaluating windowed calculations such as ranks and cumulative sums. Filtering and merging duplicates are not direct impacts of ORDER BY in this scenario, and while ordering can affect query plans, its central role here is on calculation sequence, not speed.

8. FRAMES in Window Functions

   When specifying ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING in a window function, what is the function's window for each row?

   1. It sums all previous rows only
   2. It includes the previous row, current row, and next row
   3. It covers the first and last row only
   4. It selects only the current row

   Explanation: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING defines a window that spans the prior row, the current one, and the following row for each calculation. Using only current row would need 'CURRENT ROW', not this frame. Summing all previous rows or only first and last are different window definitions.

9. Practical Application: Cumulative Total

   Which window function setup calculates a running total of 'points' in order of game_date?

   1. RANK() OVER (ORDER BY points DESC)
   2. SUM(points) OVER (ORDER BY game_date)
   3. MAX(points) GROUP BY game_date
   4. AVG(points) OVER (PARTITION BY player)

   Explanation: A cumulative or running total is calculated using SUM as a window function with ORDER BY on the relevant column, in this case, game_date. Aggregates like AVG or MAX do not produce running totals unless combined with proper window frames. Ranking functions do not sum values.

10. Combining Window Functions with Filtering

    What should you use to select only the top performer in each group after ranking with RANK() OVER (PARTITION BY group_id ORDER BY score DESC)?

    1. HAVING clause right after the window function
    2. ORDER BY rank ascending
    3. LIMIT 1 inside OVER()
    4. A subquery or CTE filtering for rank = 1

    Explanation: Only a subquery or Common Table Expression allows you to refer to calculated windowed ranks and filter where rank equals 1 for each group. The HAVING clause cannot reference window function output directly, ORDER BY merely sorts, and LIMIT cannot be used inside the OVER() clause itself.