[Solved] Given the table sales(date, amount) with rows:('2024-01-01', 100), ('2024-01-02', 150), ('2024-01-03', 200)What is the output of this query?... | PostgreSQL

PostgreSQL - Window Functions in PostgreSQL

Given the table sales(date, amount) with rows:
('2024-01-01', 100), ('2024-01-02', 150), ('2024-01-03', 200)
What is the output of this query?

SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales ORDER BY date;

A[('2024-01-01', 100, 450), ('2024-01-02', 150, 350), ('2024-01-03', 200, 200)]

B[('2024-01-01', 100, 100), ('2024-01-02', 150, 150), ('2024-01-03', 200, 200)]

C[('2024-01-01', 100, 100), ('2024-01-02', 150, 250), ('2024-01-03', 200, 450)]

D[('2024-01-01', 100, NULL), ('2024-01-02', 150, NULL), ('2024-01-03', 200, NULL)]

Step-by-Step Solution

Solution:

Step 1: Understand SUM() OVER with frame
The frame includes all rows from the start up to current row ordered by date, so it sums cumulatively.
Step 2: Calculate running totals row by row
Row 1: 100; Row 2: 100+150=250; Row 3: 250+200=450.
Final Answer:
[('2024-01-01', 100, 100), ('2024-01-02', 150, 250), ('2024-01-03', 200, 450)] -> Option C
Quick Check:
Running total sums up to current row [OK]

Quick Trick: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW sums cumulatively [OK]

Common Mistakes:

Master "Window Functions in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Given the table sales(date, amount) with rows: