date date,

pageviews int

);

insert into pages values

('2020-02-01'::date, 42),

('2020-02-02'::date, 3),

('2020-02-03'::date, 216),

('2020-02-04'::date, 186),

('2020-02-05'::date, 510),

('2020-02-06'::date, 419),

('2020-02-07'::date, 64),

('2020-02-09'::date, 230)

;

create table weights (

idx int,

weight float

);

insert into weights values

(0, 0.6),

(1, 0.24),

(2, 0.16)

;

SELECT pages.date,

max(pages.pageviews) as pageviews,

CASE WHEN pages.date - (select min(date) from pages) >= 2 THEN sum(weight * ma_pages.pageviews) END as weighted_moving_average

FROM pages

JOIN pages AS ma_pages ON pages.date - ma_pages.date BETWEEN 0 AND 2

JOIN weights ON idx = pages.date - ma_pages.date