SQL - Advanced Window Functions
Which of the following SQL queries correctly calculates the percent of total sales per product using window functions?
Which of the following SQL queries correctly calculates the percent of total sales per product using window functions?
easy📝 Syntax Q12 of 15
Step-by-Step Solution
Solution:
Step 1: Identify correct window function usage
SELECT product, sales, (sales / SUM(sales) OVER ()) * 100 AS percent_total FROM sales_table; usesSUM(sales) OVER ()to get total sales across all rows, then divides each sales value by this total.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.Final Answer:
SELECT product, sales, (sales / SUM(sales) OVER ()) * 100 AS percent_total FROM sales_table; -> Option AQuick Check:
Percent = sales / total sales * 100 [OK]
Quick Trick: 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
Master "Advanced Window Functions" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes