CODEWITHSUNDEEP
sqldatabasepostgresqlaggregate-functionswindow-functions
sparkle
sparkle

Reputation: 7390

Postgresql cumulative sum Last 3 days

I have this data and I want to compute a rolling 3-day sum like below.

Note: Missing date has to be considered. So isn't just the last 3 rows, but 3 solar days.

enter image description here enter image description here

I tried with a window function but it's not working:

SELECT
    date,
    SUM(clicks) OVER (ORDER BY DATE ASC ROWS BETWEEN DATE - INTERVAL '3 day' AND date)
FROM table

Upvotes: 0

Views: 1294

Answers (1)

Andronicus
Andronicus

Reputation: 26026

Upper bound should be current row and range:

SELECT
    date,
    SUM(clicks) OVER (ORDER BY DATE ASC range BETWEEN INTERVAL '3 day' PRECEDING AND CURRENT ROW)
FROM table

Upvotes: 1

Related Questions