Window function vs GROUP BY mental model in SQL - Performance Comparison

Time Complexity: Window function vs GROUP BY mental model

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run queries with window functions compares to those using GROUP BY.

How does the work grow as the data gets bigger for each method?

Scenario Under Consideration

Analyze the time complexity of these two queries.

-- Using GROUP BY
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department;

-- Using Window Function
SELECT employee_id, department, COUNT(*) OVER (PARTITION BY department) AS emp_count
FROM employees;
    

The first query groups rows by department and counts employees per group. The second counts employees per department but keeps all rows.

Identify Repeating Operations

Look at what repeats in each query.

• Primary operation: Scanning all rows once to count employees.
• How many times: Each row is processed once; window function adds a step to assign counts back to each row.

How Execution Grows With Input

As the number of employees (rows) grows, the work changes like this:

Input Size (n)	Approx. Operations
10	About 10 row scans and counts
100	About 100 row scans and counts
1000	About 1000 row scans and counts

Both queries scan all rows once, but the window function also assigns counts back to each row, so it does a bit more work per row.

Final Time Complexity

Time Complexity: O(n)

This means the time grows roughly in direct proportion to the number of rows in the table.

Common Mistake

[X] Wrong: "Window functions always take much longer than GROUP BY because they do more work."

[OK] Correct: Both scan all rows once; window functions add some extra steps but still scale linearly, so the difference is usually small.

Interview Connect

Understanding how these queries scale helps you explain your choices clearly and shows you know how databases handle data efficiently.

Self-Check

"What if we added an ORDER BY clause inside the window function? How would that affect the time complexity?"

Practice

1. What is the main difference between a GROUP BY clause and a window function in SQL?

easy

A. Both GROUP BY and window functions always return the same number of rows.

B. GROUP BY keeps all rows and adds summary data; window functions group rows and return one row per group.

C. GROUP BY groups rows and returns one row per group; window functions keep all rows and add summary data.

D. Window functions cannot perform calculations like sums or averages.

Solution

1. Step 1: Understand GROUP BY behavior

   GROUP BY groups rows by specified columns and returns one row per group with aggregated results.

2. Step 2: Understand window function behavior

   Window functions calculate aggregates over partitions but keep all original rows visible, adding summary columns.

3. Final Answer:

   GROUP BY groups rows and returns one row per group; window functions keep all rows and add summary data. -> Option C

4. Quick Check:

   Window functions keep detail, GROUP BY groups rows [OK]

Hint: Window functions keep rows; GROUP BY collapses them [OK]

Common Mistakes:

• Thinking window functions reduce rows like GROUP BY
• Believing GROUP BY keeps all original rows
• Assuming window functions can't do aggregates

2. Which of the following SQL queries correctly uses a window function to calculate a running total of sales per employee?

easy

A. SELECT employee_id, sale_amount, SUM(sale_amount) OVER (ORDER BY sale_date) AS running_total FROM sales GROUP BY employee_id;

B. SELECT employee_id, sale_amount, SUM(sale_amount) GROUP BY employee_id ORDER BY sale_date AS running_total FROM sales;

C. SELECT employee_id, sale_amount, SUM(sale_amount) FROM sales WINDOW (PARTITION BY employee_id ORDER BY sale_date) AS running_total;

D. SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales;

Solution

1. Step 1: Identify correct window function syntax

   SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales; uses SUM() OVER (PARTITION BY ... ORDER BY ...), which is the correct syntax for running totals per employee.

2. Step 2: Check other options for syntax errors

   Options B, C, and D misuse GROUP BY or WINDOW clauses incorrectly, causing syntax errors or wrong logic.

3. Final Answer:

   The correct query: SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales; -> Option D

4. Quick Check:

   Window function syntax = SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales; [OK]

Hint: Window functions use OVER(), GROUP BY does not [OK]

Common Mistakes:

• Using GROUP BY with window function syntax
• Misplacing WINDOW clause
• Omitting OVER() for window functions

3. Given the table sales(employee_id, sale_amount) with data:
1, 100
1, 200
2, 150
2, 50
What is the output of this query?

SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id) AS total_sales FROM sales ORDER BY employee_id, sale_amount;

medium

A. 1, 100, 3001, 200, 3002, 50, 2002, 150, 200

B. 1, 100, 1001, 200, 2002, 50, 502, 150, 150

C. 1, 100, 3001, 200, 5002, 50, 2002, 150, 350

D. 1, 100, 3001, 200, 3002, 50, 1502, 150, 150

Solution

1. Step 1: Calculate total sales per employee using window function

   For employee 1: 100 + 200 = 300; for employee 2: 150 + 50 = 200.

2. Step 2: Assign total_sales to each row without grouping

   Each row for employee 1 shows 300, each for employee 2 shows 200, preserving all rows.

3. Final Answer:

   Rows show total sales per employee repeated: 1,100,300; 1,200,300; 2,50,200; 2,150,200. -> Option A

4. Quick Check:

   Window sums repeat per row, not grouped [OK]

Hint: Window sums repeat per row; GROUP BY sums once per group [OK]

Common Mistakes:

• Confusing window sum with grouped sum
• Expecting fewer rows after window sum
• Mixing order of rows in output

4. Consider this query:

SELECT employee_id, sale_amount, SUM(sale_amount) OVER (PARTITION BY employee_id ORDER BY sale_date) AS running_total FROM sales GROUP BY employee_id, sale_amount, sale_date;

What is the main issue with this query?

medium

A. The query will run but return incorrect running totals.

B. The window function must be replaced with a GROUP BY aggregate.

C. The GROUP BY clause is unnecessary but allowed here.

D. It will cause a syntax error because window functions cannot be used with GROUP BY.

Solution

1. Step 1: Window functions execute after GROUP BY

   Window functions are applied to the result set produced after GROUP BY and HAVING, so the query runs without syntax error.

2. Step 2: GROUP BY distorts the running total

   GROUP BY employee_id, sale_amount, sale_date collapses multiple rows sharing the same values in these columns into one row each before the window function computes the running total, leading to incorrect sums if duplicate combinations exist.

3. Final Answer:

   The query will run but return incorrect running totals. -> Option A

4. Quick Check:

   GROUP BY collapses rows before window functions run [OK]

Hint: Window functions run after GROUP BY; grouping can collapse rows unexpectedly [OK]

Common Mistakes:

• Assuming syntax error when mixing GROUP BY and window functions
• Thinking GROUP BY has no effect on subsequent window calculations
• Overlooking row collapse from GROUP BY before window execution

5. You want to find each employee's sale amount along with the average sale amount of their department, but keep all individual sales visible. Which approach is best?

hard

A. Use GROUP BY department_id to get average sales, then join back to sales table.

B. Use a window function like AVG(sale_amount) OVER (PARTITION BY department_id) to add average per row.

C. Use GROUP BY employee_id to get average sales per employee.

D. Use a subquery with GROUP BY employee_id and join on employee_id.

Solution

1. Step 1: Understand requirement to keep all individual sales

   We want to keep each sale row visible, not collapse them by grouping.

2. Step 2: Choose window function to add department average per row

   Window function AVG() OVER (PARTITION BY department_id) adds average without grouping rows.

3. Step 3: Compare with other options

   Options A, C, D use GROUP BY which collapses rows or require joins, making query complex.

4. Final Answer:

   Use window function to add department average per row, keeping all sales visible. -> Option B

5. Quick Check:

   Window functions keep detail and add group info [OK]

Hint: Window functions add group info without hiding rows [OK]

Common Mistakes:

• Using GROUP BY and losing individual rows
• Overcomplicating with joins instead of window functions
• Grouping by employee instead of department