Window function vs GROUP BY mental model in SQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the total sales per product using GROUP BY.

SQL

SELECT product_id, SUM(sales) AS total_sales FROM sales_data GROUP BY [1];

Drag options to blanks, or click blank then click option'

Asales

Bproduct_id

Cdate

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by sales instead of product_id.

Forgetting to use GROUP BY when using aggregate functions.

The GROUP BY clause groups rows by the specified column. Here, grouping by product_id allows us to sum sales per product.

2fill in blank

medium

Complete the code to calculate the running total of sales per product using a window function.

SQL

SELECT product_id, sales, SUM(sales) OVER (PARTITION BY [1] ORDER BY date) AS running_total FROM sales_data;

Drag options to blanks, or click blank then click option'

Adate

Bsales

Ccustomer_id

Dproduct_id

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by sales or date instead of product_id.

Confusing PARTITION BY with GROUP BY.

The PARTITION BY clause divides the data into groups. Here, partitioning by product_id calculates running totals per product.

3fill in blank

hard

Fix the error in the query to calculate average sales per product using GROUP BY.

SQL

SELECT product_id, AVG(sales) FROM sales_data WHERE sales > 0 GROUP BY [1];

Drag options to blanks, or click blank then click option'

Adate

Bsales

Cproduct_id

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by sales instead of product_id.

Omitting GROUP BY clause.

The GROUP BY clause must include the column used to group the results. Here, product_id is correct.

4fill in blank

hard

Fill both blanks to calculate the rank of sales per product ordered by date.

SQL

SELECT product_id, sales, RANK() OVER (PARTITION BY [1] ORDER BY [2]) AS sales_rank FROM sales_data;

Drag options to blanks, or click blank then click option'

Aproduct_id

Bsales

Cdate

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using sales for partition instead of product_id.

Ordering by sales instead of date.

Partitioning by product_id groups the data per product. Ordering by date ranks sales chronologically.

5fill in blank

hard

Fill all three blanks to create a query that shows total sales per product and each sale's percentage of that total.

SQL

SELECT product_id, sales, SUM(sales) OVER (PARTITION BY [1]) AS total_sales, ROUND(sales * 100.0 / [2], 2) AS sales_percent FROM sales_data;

Drag options to blanks, or click blank then click option'

Aproduct_id

BSUM(sales) OVER (PARTITION BY product_id)

Ctotal_sales

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Using sales instead of product_id for partition.

Dividing by sales instead of total_sales.

Partition by product_id to get total sales per product. Use the window function SUM(sales) OVER (PARTITION BY product_id) to get total_sales. Then calculate the percentage by dividing sales by total_sales.

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