The WINDOW clause needs some more love

window-clause.sql

/*
    The WINDOW clause needs some more love

    DuckDB version: 1.0.0

    Bill Wallis, 2024-08-02
*/


select version();


create or replace table sales (
    sale_month date primary key,
    sale_value numeric(12, 2)
);
insert into sales
values
    ('2023-01-01', 69560.42),
    ('2023-02-01', 94217.91),
    ('2023-03-01', 89751.43),
    ('2023-04-01', 43609.30),
    ('2023-05-01', 93594.76),
    ('2023-06-01', 57048.22),
    ('2023-07-01', 28524.37),
    ('2023-08-01', 89025.31),
    ('2023-09-01',  4954.39),
    ('2023-10-01', 15839.42),
    ('2023-11-01', 39029.37),
    ('2023-12-01', 92725.02),
    ('2024-01-01', 23369.52),
    ('2024-02-01', 54181.05),
    ('2024-03-01', 49618.68),
    ('2024-04-01', 86492.45),
    ('2024-05-01', 15211.00),
    ('2024-06-01', 13226.53),
    ('2024-07-01', 41531.57),
    ('2024-08-01', 52495.28),
    ('2024-09-01', 81589.36),
    ('2024-10-01', 70031.42),
    ('2024-11-01', 19491.16),
    ('2024-12-01', 33463.45),
;
from sales;


select
    sale_month,
    sale_value,
    sum(sale_value) over last_three_months as sum_last_three_months,
    avg(sale_value) over last_three_months as avg_last_three_months,
    sum(sale_value) over (sale_year order by sale_value) as yearly_running_total,
from sales
window
    sale_year as (partition by extract(year from sale_month)),
    last_three_months as (order by sale_month rows 2 preceding)
;

This is part of the following LinkedIn post:

https://www.linkedin.com/posts/bill-wallis_sql-analyticsengineering-dataengineering-activity-7225226474671280128-Xaz0