rolling sum over the last 7 days, how to include missing dates - postresql

Question

I am trying to get something like this but I have only the first two columns:

dates          sales     rolling_sum7days           
01-01-2019     1         1
02-01-2019     3         4
03-01-2019     5         9
04-01-2019     2        11
05-01-2019     7        18
06-01-2019     8        26
08-01-2019    10        35
09-01-2019     1        32
10-01-2019     8        39       

I have come up with this but have not find a way to deal with missing values such as the sales for 07-01-2019

SELECT dates 
sum(sales) over(order by dates ROWS BETWEEN 6 preceding AND CURRENT ROW) 
from table

What should I correct? found some similar problems but they did not solved this issue. for example: this

Answer 1

demo:db<>fiddle

SELECT
    gs::date as dates,
    COALESCE(mt.sales, 0) AS sales,
    sum(sales) over(order by gs ROWS BETWEEN 6 preceding AND CURRENT ROW)
FROM
    mytable mt
RIGHT OUTER JOIN 
    generate_series('2019-01-01', '2019-01-11', interval '1 day') gs
    ON gs = mt.dates

To fill missing dates within a certain range you can use generate_series() for this date range and do an outer join.

If you do not want to fix the gs parameters then, of course, you could calculate them before, e.g. taking the MIN and MAX from your table:

demo:db<>fiddle

WITH date_bounds AS (
    SELECT min(dates), max(dates) FROM mytable
)
SELECT
    gs::date as dates,
    COALESCE(mt.sales, 0) AS sales,
    sum(sales) over(order by gs ROWS BETWEEN 6 preceding AND CURRENT ROW)
FROM
    mytable mt
RIGHT OUTER JOIN 
    generate_series(
         (SELECT min FROM date_bounds), 
         (SELECT max FROM date_bounds), 
         interval '1 day'
    ) gs
    ON gs = mt.dates