PostgreSQL | PGExercises.com | Rolling average revenue

-- https://pgexercises.com/questions/aggregates/rollingavg.html

with dates as (
    SELECT day::DATE
    FROM   generate_series(timestamp '2012-07-10', '2012-08-31', '1 day') day
),

revenues as (
    select day,
            sum(slots * (case when memid=0 then guestcost else membercost end)) as revenue
    from cd.bookings b
    right join dates d -- some days may have no revenue
    on b.starttime::DATE = d.day
    join cd.facilities f
    on b.facid = f.facid
    group by 1
    order by 1 asc
),

longlist as(
    select day,
    avg(revenue) OVER (
                ORDER BY day asc
                ROWS BETWEEN 14 PRECEDING AND CURRENT ROW)
from revenues)

select * from longlist
where day::DATE > '2012-07-31'