moving_average.sql

WITH data AS (
    SELECT
        product.id,
        i.year_month,
        date_part('year', i.year_month) as year,
        date_part('month', i.year_month) as month,
        ( random()*100 )::integer as value
    FROM generate_series('2018-01-01'::date, '2018-12-01'::date, '1 month') as i(year_month)
    CROSS JOIN generate_series(1,1,1) product(id)
),
date_range AS (
    SELECT
        date_part('year', i.year_month) as year,
        date_part('month', i.year_month) as month
    FROM generate_series('2018-01-01'::date, '2019-12-01'::date, '1 month') as i(year_month)
),
products_with_dates AS (
    SELECT
        products.id,
        dr.year,
        dr.month,
        d.value
    FROM (
        SELECT DISTINCT id FROM data
    ) products
    CROSS JOIN date_range dr
    LEFT JOIN data d ON (products.id = d.id AND dr.year = d.year AND dr.month = d.month)
)
SELECT * FROM products_with_dates ORDER BY id, year, month;