You have a sales table with columns region, sale_date, and amount. You want to calculate a running total of sales per region ordered by sale_date. Which query correctly achieves this?
hard📝 Application Q15 of 15
SQL - Advanced Window Functions
You have a sales table with columns region, sale_date, and amount. You want to calculate a running total of sales per region ordered by sale_date. Which query correctly achieves this?
ASELECT region, sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS running_total FROM sales;
BSELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY sale_date ORDER BY region) AS running_total FROM sales;
CSELECT region, sale_date, amount, SUM(amount) FROM sales GROUP BY region, sale_date ORDER BY sale_date;
DSELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total FROM sales;
Step-by-Step Solution
Solution:
Step 1: Understand requirement for running total per region
We need to reset running total for each region, so PARTITION BY region is required.
Step 2: Check ORDER BY for running total
ORDER BY sale_date inside OVER() ensures running total accumulates by date within each region.
Step 3: Evaluate options
SELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total FROM sales; correctly uses PARTITION BY region and ORDER BY sale_date inside SUM() OVER().
Final Answer:
SELECT region, sale_date, amount, SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total FROM sales; -> Option D
Quick Check:
PARTITION BY region + ORDER BY sale_date = running total per region [OK]
Quick Trick:Use PARTITION BY for groups, ORDER BY for running total order [OK]
Common Mistakes:
Omitting PARTITION BY to separate groups
Using GROUP BY instead of window functions
Swapping ORDER BY columns inside OVER()
Master "Advanced Window Functions" in SQL
9 interactive learning modes - each teaches the same concept differently
