It's a simple problem to calculate cumulative sum. But the question is how to do so in SQL?
The first idea I've came up with was using AGG() OVER , and I tried OVER(PARTITION BY turn) which was wrong. When I usedOVER(ORDER BY turn) , this gives me the correct result.
The whole code is below:
SELECT person_name
FROM (
SELECT person_name, turn, SUM(weight) OVER (ORDER BY turn) AS cum
FROM Queue
) AS Temp
WHERE CUM <= 1000
ORDER BY cum DESC
LIMIT 1Then why was it correct? I asked this to ChatGPT:
The SUM() OVER() construct in SQL gives a cumulative sum because of the way the OVER clause works.
When you use SUM() OVER (ORDER BY column), it calculates the sum in the order specified by the column, and for each row, it includes all rows up to and including that row in the sum. This is why it gives a cumulative sum.
Here’s a simple example:
Suppose you have a table sales:
| sale_id | sale_amount |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
If you run the query SELECT sale_id, sale_amount, SUM(sale_amount) OVER (ORDER BY sale_id) as cumulative_sum FROM sales;, you would get:
| sale_id | sale_amount | cumulative_sum |
|---|---|---|
| 1 | 100 | 100 |
| 2 | 200 | 300 |
| 3 | 300 | 600 |
As you can see, for each row, the cumulative_sum column contains the sum of sale_amount for that row and all preceding rows, in the order of sale_id. This is why SUM() OVER (ORDER BY column) gives a cumulative sum.
In summary, OVER window function can count on preceding rows and a current row. Simple, but powerful!