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.