postgreSQL, first date when cummulative sum reaches mark

Question

I have the following sample table And the output should be the first date (for each id) when cum_rev reaches the 100 mark.

I tried the following, because I taught with group bz trick and the where condition i will only get the first occurrence of value higher than 100.

SELECT id
    ,pd
    ,cum_rev
FROM (
    SELECT id
        ,pd
        ,rev
        ,SUM(rev) OVER (
            PARTITION BY id 
            ORDER BY pd
            ) AS cum_rev
    FROM tab1
    )
WHERE cum_rev >= 100
GROUP BY id

But it is not working, and I get the following error. And also when I add an alias is not helping

ERROR: subquery in FROM must have an alias LINE 4: FROM ( ^ HINT: For example, FROM (SELECT ...) [AS] foo.

So the desired output is:

2   2015-04-02  135.70
3   2015-07-03  102.36

Do I need another approach? Can anyone help? Thanks

score 1 · Accepted Answer · answered Feb 09 '21 at 11:44

demo:db<>fiddle

SELECT
    id, total
FROM (
    SELECT
        *,
        SUM(rev) OVER (PARTITION BY id ORDER BY pd) - rev as prev_total,
        SUM(rev) OVER (PARTITION BY id ORDER BY pd) as total
    FROM tab1
) s
WHERE total >= 100 AND prev_total < 100

You can use the cumulative SUM() window function for each id group (partition). To find the first which goes over a threshold you need to check the previous value for being under the threshold while the current one meets it.

PS: You got the error because your subquery is missing an alias. In my example its just s

postgreSQL, first date when cummulative sum reaches mark

1 Answers1