How to group by when using a modulo

Question

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 - 1st attempt (failed) https://i.sstatic.net/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

EDIT: the following code meets my needs. The 'revenue' is summed using the most recent quarter and the 3 quarters thereafter.

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

Answer 1

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