Percent of total with window functions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate percent of total sales per product

Given the sales table with columns product_id and amount, which query correctly calculates the percent of total sales for each product?

SQL

SELECT product_id, amount, 
       100.0 * amount / SUM(amount) OVER () AS percent_of_total
FROM sales;

A SELECT product_id, amount, amount / SUM(amount) OVER (PARTITION BY product_id) AS percent_of_total FROM sales;

B SELECT product_id, amount, 100.0 * SUM(amount) OVER () / amount AS percent_of_total FROM sales;

C SELECT product_id, amount, 100.0 * amount / SUM(amount) OVER () AS percent_of_total FROM sales;

D SELECT product_id, amount, 100.0 * amount / SUM(amount) FROM sales GROUP BY product_id;

💡 Hint

Use a window function without partitioning to get the total sum over all rows.

Option C correctly divides each amount by the total sum of all amounts using a window function without partitioning, giving the percent of total sales per product.

❓ query_result

intermediate

Percent of total with partition by category

Which query calculates the percent of total sales within each category using window functions? The table sales has columns category and amount.

SQL

SELECT category, amount, 
       100.0 * amount / SUM(amount) OVER (PARTITION BY category) AS percent_of_category_total
FROM sales;

A SELECT category, amount, 100.0 * amount / SUM(amount) OVER (PARTITION BY category) AS percent_of_category_total FROM sales;

B SELECT category, amount, 100.0 * SUM(amount) OVER () / amount AS percent_of_category_total FROM sales;

C SELECT category, amount, amount / SUM(amount) OVER () AS percent_of_category_total FROM sales;

D SELECT category, amount, 100.0 * amount / SUM(amount) FROM sales GROUP BY category;

💡 Hint

Partition the sum by category to get totals per category.

Option A uses a window function partitioned by category to calculate the percent of total sales within each category.

📝 Syntax

advanced

Identify the syntax error in percent calculation query

Which option contains a syntax error when calculating percent of total sales using window functions?

SQL

SELECT product_id, amount, 100 * amount / SUM(amount) OVER () AS percent_of_total FROM sales;

A SELECT product_id, amount, 100 * amount / SUM(amount) OVER () AS percent_of_total FROM sales;

B SELECT product_id, amount, 100 * amount / SUM(amount) OVER PARTITION BY () AS percent_of_total FROM sales;

C SELECT product_id, amount, 100.0 * amount / SUM(amount) OVER () AS percent_of_total FROM sales;

D SELECT product_id, amount, 100 * amount / SUM(amount) OVER (PARTITION BY product_id) AS percent_of_total FROM sales;

💡 Hint

Check the syntax of the OVER clause and partitioning.

Option B has invalid syntax: 'OVER PARTITION BY ()' is incorrect. The correct syntax is 'OVER ()' or 'OVER (PARTITION BY column)'.

❓ optimization

advanced

Optimize percent of total calculation for large datasets

Which query is the most efficient way to calculate percent of total sales per product when the sales table is very large?

A WITH total AS (SELECT SUM(amount) AS total_amount FROM sales) SELECT product_id, amount, 100.0 * amount / total_amount AS percent_of_total FROM sales, total;

B SELECT product_id, amount, 100.0 * amount / (SELECT SUM(amount) FROM sales) AS percent_of_total FROM sales;

C SELECT product_id, amount, 100.0 * amount / SUM(amount) OVER () AS percent_of_total FROM sales;

D SELECT product_id, amount, 100.0 * amount / SUM(amount) FROM sales GROUP BY product_id;

💡 Hint

Calculate the total sum once, then join to avoid repeated aggregation.

Option A calculates the total sum once in a CTE and joins it, avoiding repeated aggregation for each row, which is more efficient on large datasets.

🧠 Conceptual

expert

Understanding window function behavior in percent calculations

Consider the query:

SELECT product_id, amount, 100.0 * amount / SUM(amount) OVER (PARTITION BY category ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_percent FROM sales;

What does the running_percent column represent?

A The percent of the current amount relative to the total sales of all products on the current date only.

B The percent of the current amount relative to the total sales of all categories regardless of date.

C The percent of the current amount relative to the total sales in the category, ignoring date order.

D The percent of the current amount relative to the total sales in the same category up to the current date (running total percent).

💡 Hint

Look at the window frame defined by ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

The window frame defines a running total within each category ordered by date, so the percent is relative to the cumulative sum up to the current row's date.

Practice

1. What does the SQL window function SUM(column) OVER () compute in a query?

easy

A. The maximum value of the column

B. The total sum of the column across all rows without grouping

C. The average of the column values

D. The sum of the column for each group defined by GROUP BY

Solution

1. Step 1: Understand window function syntax

   The syntax SUM(column) OVER () means sum over all rows without partitioning or grouping.

2. Step 2: Compare with GROUP BY sum

   Unlike GROUP BY, this sums the entire column across all rows, returning the same total for each row.

3. Final Answer:

   The total sum of the column across all rows without grouping -> Option B

4. Quick Check:

   SUM() OVER () = total sum [OK]

Hint: SUM() OVER () gives total sum for all rows, no grouping [OK]

Common Mistakes:

• Confusing window sum with GROUP BY sum
• Thinking it calculates average
• Assuming it returns max value

2. Which of the following SQL queries correctly calculates the percent of total sales per product using window functions?

easy

A. SELECT product, sales, (sales / SUM(sales) OVER ()) * 100 AS percent_total FROM sales_table;

B. SELECT product, sales, (SUM(sales) OVER () / sales) * 100 AS percent_total FROM sales_table;

C. SELECT product, sales, (sales / SUM(sales)) * 100 AS percent_total FROM sales_table GROUP BY product;

D. SELECT product, sales, (sales / COUNT(*) OVER ()) * 100 AS percent_total FROM sales_table;

Solution

1. Step 1: Identify correct window function usage

   SELECT product, sales, (sales / SUM(sales) OVER ()) * 100 AS percent_total FROM sales_table; uses SUM(sales) OVER () to get total sales across all rows, then divides each sales value by this total.

2. Step 2: Check other options for errors

   SELECT product, sales, (SUM(sales) OVER () / sales) * 100 AS percent_total FROM sales_table; divides total by sales (wrong direction). SELECT product, sales, (sales / SUM(sales)) * 100 AS percent_total FROM sales_table GROUP BY product; uses SUM(sales) without OVER(), which requires GROUP BY and won't work as intended. SELECT product, sales, (sales / COUNT(*) OVER ()) * 100 AS percent_total FROM sales_table; divides sales by count of rows, not sum.

3. Final Answer:

   SELECT product, sales, (sales / SUM(sales) OVER ()) * 100 AS percent_total FROM sales_table; -> Option A

4. Quick Check:

   Percent = sales / total sales * 100 [OK]

Hint: Divide sales by SUM() OVER () and multiply by 100 [OK]

Common Mistakes:

• Reversing numerator and denominator
• Using SUM() without OVER() in window context
• Using COUNT() instead of SUM() for total

3. Given the table sales_data with columns product and amount, what is the output of this query?

SELECT product, amount, ROUND((amount / SUM(amount) OVER ()) * 100, 1) AS percent_total FROM sales_data ORDER BY product;

Assume the data:
product | amount
-------|-------
A | 50
B | 30
C | 20

medium

A. [{"product": "A", "amount": 50, "percent_total": 100.0}, {"product": "B", "amount": 30, "percent_total": 100.0}, {"product": "C", "amount": 20, "percent_total": 100.0}]

B. [{"product": "A", "amount": 50, "percent_total": 33.3}, {"product": "B", "amount": 30, "percent_total": 20.0}, {"product": "C", "amount": 20, "percent_total": 13.3}]

C. [{"product": "A", "amount": 50, "percent_total": 25.0}, {"product": "B", "amount": 30, "percent_total": 15.0}, {"product": "C", "amount": 20, "percent_total": 10.0}]

D. [{"product": "A", "amount": 50, "percent_total": 50.0}, {"product": "B", "amount": 30, "percent_total": 30.0}, {"product": "C", "amount": 20, "percent_total": 20.0}]

Solution

1. Step 1: Calculate total amount

   Total = 50 + 30 + 20 = 100.

2. Step 2: Calculate percent for each product

   Percent = (amount / total) * 100 rounded to 1 decimal:
   A: (50/100)*100 = 50.0
   B: (30/100)*100 = 30.0
   C: (20/100)*100 = 20.0

3. Final Answer:

   [{"product": "A", "amount": 50, "percent_total": 50.0}, {"product": "B", "amount": 30, "percent_total": 30.0}, {"product": "C", "amount": 20, "percent_total": 20.0}] -> Option D

4. Quick Check:

   Percent totals sum to 100% [OK]

Hint: Sum amounts, divide each by total, multiply by 100 [OK]

Common Mistakes:

• Not rounding results
• Using COUNT() instead of SUM()
• Calculating percent incorrectly (e.g., reversed division)

4. You wrote this query to calculate percent of total sales:

SELECT product, sales, (sales / SUM(sales)) * 100 AS percent_total FROM sales_table;

But it gives an error. What is the problem?

medium

A. SUM(sales) requires a GROUP BY clause or window function syntax

B. sales column does not exist

C. Multiplying by 100 is invalid syntax

D. Missing WHERE clause

Solution

1. Step 1: Analyze use of SUM() without OVER()

   SUM(sales) without GROUP BY or window function context causes error because SQL doesn't know how to aggregate without grouping.

2. Step 2: Correct usage

   Use either GROUP BY product with SUM(sales) or use window function SUM(sales) OVER () to get total sum without grouping.

3. Final Answer:

   SUM(sales) requires a GROUP BY clause or window function syntax -> Option A

4. Quick Check:

   SUM() needs GROUP BY or OVER() [OK]

Hint: Use SUM() OVER () or GROUP BY with SUM() [OK]

Common Mistakes:

• Ignoring need for GROUP BY with aggregate functions
• Assuming multiplication by 100 causes error
• Thinking WHERE clause is mandatory here

5. You have a table orders with columns category and amount. Write a query to show each category, total amount per category, and percent of total amount across all categories. Which query is correct?

hard

A. SELECT category, SUM(amount) AS category_total, (SUM(amount) / COUNT(*) OVER ()) * 100 AS percent_total FROM orders GROUP BY category;

B. SELECT category, amount, (amount / SUM(amount) OVER ()) * 100 AS percent_total FROM orders ORDER BY category;

C. SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(SUM(amount)) OVER ()) * 100 AS percent_total FROM orders GROUP BY category ORDER BY category;

D. SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(amount)) * 100 AS percent_total FROM orders GROUP BY category;

Solution

1. Step 1: Calculate total per category

   Use SUM(amount) GROUP BY category to get total per category.

2. Step 2: Calculate percent of total across all categories

   Use window function SUM(SUM(amount)) OVER () to get grand total across all categories, then divide category total by this and multiply by 100.

3. Step 3: Verify query correctness

   SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(SUM(amount)) OVER ()) * 100 AS percent_total FROM orders GROUP BY category ORDER BY category; correctly combines GROUP BY with window function for total sum. SELECT category, amount, (amount / SUM(amount) OVER ()) * 100 AS percent_total FROM orders ORDER BY category; uses amount per row, not category total. SELECT category, SUM(amount) AS category_total, (SUM(amount) / COUNT(*) OVER ()) * 100 AS percent_total FROM orders GROUP BY category; incorrectly uses COUNT() for total. SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(amount)) * 100 AS percent_total FROM orders GROUP BY category; divides sum by itself, always 100%.

4. Final Answer:

   SELECT category, SUM(amount) AS category_total, (SUM(amount) / SUM(SUM(amount)) OVER ()) * 100 AS percent_total FROM orders GROUP BY category ORDER BY category; -> Option C

5. Quick Check:

   Percent = category sum / total sum * 100 [OK]

Hint: Use GROUP BY with SUM() and window SUM(SUM(amount)) OVER () for total [OK]

Common Mistakes:

• Using row amount instead of category sum
• Using COUNT() instead of SUM() for total
• Dividing sum by itself always gives 100%