How to calculate running total per month?

Question

I have a table in PostgreSQL:

date	value
'2020-06-02'	150
'2020-06-03'	7891
'2020-07-02'	392
...	...

I need to get running total of numbers in value column for each date in table. The sum of value in running total has to be calculated over the date range between the first date of month of the date in row and date in the row.

Examples:

• For date '2020-06-02' in the first row the sum of value column has to be calculated over the range between '2020-06-01' and current date ('2020-06-02'), including number for current date.
• For the date in the second row the sum of numbers in value column has to be calculated over the date range between '2020-06-01' and current date ('2020-06-03'), including number for '2020-06-03'.
• For the third row the sum of numbers in value column has to be calculated over the date range between '2020-07-01' and '2020-07-02'.
• and so on...

How to achieve this? With window functions?

Answer 1

Actually:

SELECT date
     , sum(value) OVER (PARTITION BY date_trunc('month', date) ORDER BY date)
FROM   tbl;

Note that peer rows (according to your partition and sort order) get the same result. Meaning, duplicate days each get the same running sum including all of their values.