Percent of total with window functions in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the percent of total sales for each product.

SQL

SELECT product_id, sales_amount, sales_amount [1] SUM(sales_amount) OVER () AS percent_of_total FROM sales;

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

A+

B*

C/

D-

Attempts:

3 left

💡 Hint

Common Mistakes

Using multiplication instead of division.

Forgetting to use the window function SUM() OVER ().

We divide each sales_amount by the total sales (SUM over all rows) to get the percent of total.

2fill in blank

medium

Complete the code to calculate the percent of total sales rounded to two decimal places.

SQL

SELECT product_id, ROUND(sales_amount [1] SUM(sales_amount) OVER () * 100, 2) AS percent_of_total FROM sales;

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

A/

B+

C-

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using addition or subtraction instead of division.

Multiplying before dividing.

We divide sales_amount by total sales, multiply by 100 to get a percentage, then round to 2 decimals.

3fill in blank

hard

Fix the error in the code to correctly calculate the percent of total sales per category.

SQL

SELECT category, sales_amount, sales_amount [1] SUM(sales_amount) OVER (PARTITION BY category) AS percent_of_category_total FROM sales;

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

A+

B-

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using addition or multiplication instead of division.

Not using PARTITION BY in the window function.

To get the percent of total per category, divide sales_amount by the sum of sales_amount partitioned by category.

4fill in blank

hard

Fill both blanks to calculate the percent of total sales and alias the result as percent_total.

SQL

SELECT product_id, sales_amount, (sales_amount [1] SUM(sales_amount) OVER ()) * 100 AS [2] FROM sales;

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

A/

B*

Cpercent_total

Dpercent

Attempts:

3 left

💡 Hint

Common Mistakes

Using multiplication instead of division.

Using unclear or missing alias names.

Divide sales_amount by total sales, multiply by 100, and name the column percent_total.

5fill in blank

hard

Fill all three blanks to calculate the percent of total sales per region, rounded to 1 decimal place, and alias it as pct_region.

SQL

SELECT region, sales_amount, ROUND((sales_amount [1] SUM(sales_amount) OVER (PARTITION BY region)) * [2], [3]) AS pct_region FROM sales;

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

A/

B100

C1

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting PARTITION BY region.

Forgetting to multiply by 100.

Rounding to wrong decimal places.

Divide sales_amount by total sales per region, multiply by 100 to get percent, then round to 1 decimal place.