sqladvanced15 minutes

Fix the Bug in SQL Query Calculating Running Totals with Incorrect Window Frames

Identify and fix the logical error in the given SQL query that attempts to calculate running totals of sales per customer, but returns incorrect results due to improper window frame specification.

Challenge prompt

You are provided with a SQL query intended to calculate the running total of sales amounts per customer ordered by sale date. However, the query returns incorrect running totals, especially when multiple sales occur on the same date or for customers with multiple transactions. Analyze the query, identify the bug related to the window frame definition, and correct it to ensure accurate running totals per customer.

Guidance

• Review how window functions and frames work, especially the ROWS and RANGE clauses within OVER()
• Focus on partitioning by customer and ordering by sale date so that running totals accumulate correctly
• Consider how the frame affects duplicates in ordering column or when multiple rows have the same date

Hints

• The default frame for ORDER BY in window functions may not behave as expected with RANGE UNBOUNDED PRECEDING
• Switching to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW can fix cumulative totals when there are duplicates in order key
• Check if the ORDER BY column has duplicate timestamps or repeated values

Starter code

SELECT customer_id,
       sale_date,
       sale_amount,
       SUM(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date RANGE UNBOUNDED PRECEDING) AS running_total
FROM sales
ORDER BY customer_id, sale_date;

Expected output

A result set where each row shows customer_id, sale_date, sale_amount, and running_total with running_total correctly summing sale_amounts from the first sale_date up to the current sale_date per customer, accounting properly for multiple sales on the same date.

Core concepts

window functionswindow framesSQL debugging

Challenge a Friend

Send this duel to someone else and see if they can solve it.