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.