How to fill in missing dates and keep running sum of values using SQL SELECT?

There's a table with values for certain dates:

   date    |    value
-------------------------
2019-01-01 |     50
2019-01-03 |     100
2019-01-06 |     150
2019-01-08 |     20

But what I'm hoping to do is create a time series with a running sum from the first to the last date so:

   date    |    value
-------------------------
2019-01-01 |     50    
2019-01-02 |     50
2019-01-03 |     150   (+100)
2019-01-04 |     150
2019-01-05 |     150
2019-01-06 |     300   (+150)
2019-01-07 |     300   
2019-01-08 |     320   (+20)

The only constraint is that all tables are read only, so I can only query them and not modify them.

Does anyone know if this might be possible?

1 answer

  • answered 2019-12-06 20:21 Gordon Linoff

    You can generate the dates using arrays and unnest(). The rest is a left join and cumulative sum:

    select dates.dte,
           sum(t.value) over (order by dates.dte)
    from (values (sequence(from_iso86001_date('2019-01-01'),
                           from_iso86001_date('2019-01-08'),
                           interval '1' day
                          )
                 )
         ) v(date_array) cross join
         unnest(v.date_array) dates(dte) left join
         t
         on t.date = dates.dte;