How to group by when using a modulo-CodePudding

For each company, I want to sum the revenue for the 4 most recent quarters, then the 4 subsequent ones, and so on (see screenshot attached for details). How can I do that? SQL query and result https://i.stack.imgur.com/wWhhb.png

SELECT 
    ticker, 
    period_end_date, 
    revenue,
    1  ((rn - 1) % 4) AS test
FROM (
    SELECT 
        ticker, 
        period_end_date,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY period_end_date DESC) rn
    FROM "ANALYTICS"."vQUARTERLY_MASTER_MATERIALIZED"
    --WHERE ticker = 'ACN'
    ORDER BY ticker
) q

CodePudding user response：

You can try this :

SELECT ticker 
     , period_end_date 
     , total_revenue
FROM (
    SELECT ticker 
         , period_end_date
         , SUM(revenue) OVER (PARTITION BY ticker ORDER BY period_end_date DESC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS total_revenue
         , max(period_end_date) OVER (PARTITION BY ticker) AS period_end_date_max
    FROM "ANALYTICS"."vQUARTERLY_MASTER_MATERIALIZED"
    --WHERE ticker = 'ACN
     ) q
 WHERE EXTRACT(MONTH FROM period_end_date) = EXTRACT(MONTH FROM period_end_date_max)
 ORDER BY ticker, period_end_date ASC

CodePudding user response：

The following code meets my needs

SELECT 
    ticker, 
    period_end_date, 
    SUM(revenue) OVER (PARTITION BY ticker ORDER BY period_end_date DESC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) AS total_revenue
    FROM "ANALYTICS"."vQUARTERLY_MASTER_MATERIALIZED"
--WHERE ticker = 'ACN'
ORDER BY ticker