Rolling sum previous 12 months grouped by 2 dimensions (SQL- Snowflake)-CodePudding

I have the following table structure available in the C:

I am struggling in Snowflake with a query that should show me the the sum of previous 12 months for every distinct month in the table split into three dimensions .

The way reporting date 01.08.2022 for region='US' and type=1 is calculated: it is the sum of the past 12 months' row of "revenue_12_months" = 4000 45433 45777 8866 4000 6678 2456 6677 6677 7744 6775 7755

    WITH
    indata(dt,region,type,revenue) AS (

          SELECT DATE '2021-04-01','US','Type 1',4000 UNION ALL SELECT DATE '2021-05-01','Europe','Type 2',5777
UNION ALL SELECT DATE '2021-05-01','US','Type 1',45433 UNION ALL SELECT DATE '2021-07-01','Europe','Type 2',8955
UNION ALL SELECT DATE '2021-06-01','US','Type 1',45777 UNION ALL SELECT DATE '2021-09-01','Asia','Type 1',7533
UNION ALL SELECT DATE '2021-07-01','US','Type 1',8866 UNION ALL SELECT DATE '2021-11-01','Asia','Type 2',5534
UNION ALL SELECT DATE '2021-08-01','US','Type 1',4000 UNION ALL SELECT DATE '2022-01-01','Asia','Type 1',7244
UNION ALL SELECT DATE '2021-09-01','US','Type 1',6678 UNION ALL SELECT DATE '2022-03-01','Asia','Type 1',5654
UNION ALL SELECT DATE '2021-10-01','US','Type 1',2456 UNION ALL SELECT DATE '2022-05-01','Asia','Type 1',4525
UNION ALL SELECT DATE '2021-11-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-07-01','Asia','Type 1',6654
UNION ALL SELECT DATE '2021-12-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-09-01','Asia','Type 2',5754
UNION ALL SELECT DATE '2022-01-01','US','Type 1',7744 UNION ALL SELECT DATE '2022-11-01','Asia','Type 2',5644
UNION ALL SELECT DATE '2022-02-01','US','Type 1',6775 UNION ALL SELECT DATE '2023-01-01','Asia','Type 2',6777
UNION ALL SELECT DATE '2022-03-01','US','Type 1',7755

    )
    
    select dt,region,type, SUM(revenue) OVER (ORDER BY dt,region,type ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) revenue_12_months
    from indata

CodePudding user response：

could it be that you simply forgot the GROUP BY part in your window function?

     WITH
    indata(dt,region,type,revenue) AS (

          SELECT DATE '2021-04-01','US','Type 1',4000 UNION ALL SELECT DATE '2021-05-01','Europe','Type 2',5777
UNION ALL SELECT DATE '2021-05-01','US','Type 1',45433 UNION ALL SELECT DATE '2021-07-01','Europe','Type 2',8955
UNION ALL SELECT DATE '2021-06-01','US','Type 1',45777 UNION ALL SELECT DATE '2021-09-01','Asia','Type 1',7533
UNION ALL SELECT DATE '2021-07-01','US','Type 1',8866 UNION ALL SELECT DATE '2021-11-01','Asia','Type 2',5534
UNION ALL SELECT DATE '2021-08-01','US','Type 1',4000 UNION ALL SELECT DATE '2022-01-01','Asia','Type 1',7244
UNION ALL SELECT DATE '2021-09-01','US','Type 1',6678 UNION ALL SELECT DATE '2022-03-01','Asia','Type 1',5654
UNION ALL SELECT DATE '2021-10-01','US','Type 1',2456 UNION ALL SELECT DATE '2022-05-01','Asia','Type 1',4525
UNION ALL SELECT DATE '2021-11-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-07-01','Asia','Type 1',6654
UNION ALL SELECT DATE '2021-12-01','US','Type 1',6677 UNION ALL SELECT DATE '2022-09-01','Asia','Type 2',5754
UNION ALL SELECT DATE '2022-01-01','US','Type 1',7744 UNION ALL SELECT DATE '2022-11-01','Asia','Type 2',5644
UNION ALL SELECT DATE '2022-02-01','US','Type 1',6775 UNION ALL SELECT DATE '2023-01-01','Asia','Type 2',6777
UNION ALL SELECT DATE '2022-03-01','US','Type 1',7755

    )
    
    select dt,region,type, SUM(revenue) OVER (PARTITION BY region, type ORDER BY dt,region,type ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) revenue_12_months
    from indata
    ORDER BY REGION, TYPE, DT;

Best regards,

CodePudding user response：

You want it per region and type, so that needs to part of the partition by clause

sum(revenue) over (partition by region, type order by dt rows between 11 preceding and current row)