Oracle SQL revenue YTD computation

Question

I want to write an oracle SQL query to compute monthly YTD revenue (cumulative sum) for all possible combinations of the given dimensions. There are also some months where there are no transactions and hence no revenue, in this case the previous month YTD revenue must be displayed for that dimension combination. Given table:

| Month   | site | channel | type | revenue |
| -----   | ---- | ------- | ---- | ------- |
| 2017-02 | abc  |    1    |  A   |   50    |
| 2017-04 | abc  |    2    |  B   |   100   |
| 2018-12 | xyz  |    1    |  A   |   150   |

Sample Desired output:

| Month   | site | channel | type | ytd revenue |
| -----   | ---- | ------- | ---- | ------- |
| 2017-01 | abc  |    1    |  A   |    0    |
| 2017-02 | abc  |    1    |  A   |    50   |
| 2017-03 | abc  |    1    |  A   |    50   |
| 2017-04 | abc  |    1    |  A   |    50   |
| ------  | ---  |    --   |  --  |   ---   |
| 2018-12 | abc  |    1    |  A   |  1000   |
| -----   | --   |   --    |  --  |   ---   |
| 2017-04 | abc  |    2    |  A   |    100  |
| ----    | ---  |    -    |  -   |    --   |
| 2018-12 | abc  |    2    |  A   |    10   |
| ---     | --   |    -    |  -   |    --   |
| 2018-12 | xyz  |    1    |  A   |   150   |

the fiscal year starts in 1st month and ends in 12th month. So the cumulative sum or YTD revenue must be from 1st month to 12th month every year for all dimension combinations as illustrated in the sample output above.

Answer 1

Use a PARTITION OUTER JOIN:

SELECT ADD_MONTHS( t.year, c.month - 1 ) AS month,
       t.site,
       t.channel,
       t.type,
       SUM( COALESCE( t.revenue, 0 ) ) OVER (
         PARTITION BY t.site, t.channel, t.type, t.year
         ORDER BY c.month
       ) AS ytd_revenue
FROM   (
         SELECT LEVEL AS month
         FROM   DUAL
         CONNECT BY LEVEL <= 12
       ) c
       LEFT OUTER JOIN (
         SELECT t.*,
                TRUNC( month, 'YY' ) AS year
         FROM   table_name t
       ) t
       PARTITION BY ( site, channel, type, year )
       ON ( c.month = EXTRACT( MONTH FROM t.month ) );

Which, for the sample data:

CREATE TABLE table_name ( Month, site, channel, type, revenue ) AS
SELECT DATE '2017-02-01', 'abc', 1, 'A',  50 FROM DUAL UNION ALL
SELECT DATE '2017-04-01', 'abc', 2, 'B', 100 FROM DUAL UNION ALL
SELECT DATE '2018-12-01', 'xyz', 1, 'A', 150 FROM DUAL;

Outputs:

MONTH               | SITE | CHANNEL | TYPE | YTD_REVENUE
:------------------ | :--- | ------: | :--- | ----------:
2017-01-01 00:00:00 | abc  |       1 | A    |           0
2017-02-01 00:00:00 | abc  |       1 | A    |          50
2017-03-01 00:00:00 | abc  |       1 | A    |          50
2017-04-01 00:00:00 | abc  |       1 | A    |          50
2017-05-01 00:00:00 | abc  |       1 | A    |          50
2017-06-01 00:00:00 | abc  |       1 | A    |          50
2017-07-01 00:00:00 | abc  |       1 | A    |          50
2017-08-01 00:00:00 | abc  |       1 | A    |          50
2017-09-01 00:00:00 | abc  |       1 | A    |          50
2017-10-01 00:00:00 | abc  |       1 | A    |          50
2017-11-01 00:00:00 | abc  |       1 | A    |          50
2017-12-01 00:00:00 | abc  |       1 | A    |          50
2017-01-01 00:00:00 | abc  |       2 | B    |           0
2017-02-01 00:00:00 | abc  |       2 | B    |           0
2017-03-01 00:00:00 | abc  |       2 | B    |           0
2017-04-01 00:00:00 | abc  |       2 | B    |         100
2017-05-01 00:00:00 | abc  |       2 | B    |         100
2017-06-01 00:00:00 | abc  |       2 | B    |         100
2017-07-01 00:00:00 | abc  |       2 | B    |         100
2017-08-01 00:00:00 | abc  |       2 | B    |         100
2017-09-01 00:00:00 | abc  |       2 | B    |         100
2017-10-01 00:00:00 | abc  |       2 | B    |         100
2017-11-01 00:00:00 | abc  |       2 | B    |         100
2017-12-01 00:00:00 | abc  |       2 | B    |         100
2018-01-01 00:00:00 | xyz  |       1 | A    |           0
2018-02-01 00:00:00 | xyz  |       1 | A    |           0
2018-03-01 00:00:00 | xyz  |       1 | A    |           0
2018-04-01 00:00:00 | xyz  |       1 | A    |           0
2018-05-01 00:00:00 | xyz  |       1 | A    |           0
2018-06-01 00:00:00 | xyz  |       1 | A    |           0
2018-07-01 00:00:00 | xyz  |       1 | A    |           0
2018-08-01 00:00:00 | xyz  |       1 | A    |           0
2018-09-01 00:00:00 | xyz  |       1 | A    |           0
2018-10-01 00:00:00 | xyz  |       1 | A    |           0
2018-11-01 00:00:00 | xyz  |       1 | A    |           0
2018-12-01 00:00:00 | xyz  |       1 | A    |         150

Or, if you want the complete date range rather than just each year:

WITH calendar ( month ) AS (
  SELECT ADD_MONTHS( start_month, LEVEL - 1 )
  FROM   (
    SELECT MIN( ADD_MONTHS( TRUNC( ADD_MONTHS( month, -3 ), 'YY' ), 3 ) ) AS start_month,
           ADD_MONTHS( MAX( TRUNC( ADD_MONTHS( month, -3 ), 'YY' ) ), 14 ) AS end_month
    FROM   table_name
  )
  CONNECT BY
          ADD_MONTHS( start_month, LEVEL - 1 ) <= end_month
)
SELECT TO_CHAR( c.month, 'YYYY-MM' ) AS month,
       t.site,
       t.channel,
       t.type,
       SUM( COALESCE( t.revenue, 0 ) ) OVER (
         PARTITION BY t.site, t.channel, t.type, TRUNC( c.month, 'YY' )
         ORDER BY c.month
       ) AS ytd_revenue
FROM   calendar c
       LEFT OUTER JOIN (
         SELECT t.*,
                TRUNC( month, 'YY' ) AS year
         FROM   table_name t
       ) t
       PARTITION BY ( site, channel, type )
       ON ( c.month = t.month )
ORDER BY
       site, channel, type, month;

Which outputs:

MONTH               | SITE | CHANNEL | TYPE | YTD_REVENUE
:------------------ | :--- | ------: | :--- | ----------:
2017-01-01 00:00:00 | abc  |       1 | A    |           0
2017-02-01 00:00:00 | abc  |       1 | A    |          50
2017-03-01 00:00:00 | abc  |       1 | A    |          50
2017-04-01 00:00:00 | abc  |       1 | A    |          50
2017-05-01 00:00:00 | abc  |       1 | A    |          50
2017-06-01 00:00:00 | abc  |       1 | A    |          50
2017-07-01 00:00:00 | abc  |       1 | A    |          50
2017-08-01 00:00:00 | abc  |       1 | A    |          50
2017-09-01 00:00:00 | abc  |       1 | A    |          50
2017-10-01 00:00:00 | abc  |       1 | A    |          50
2017-11-01 00:00:00 | abc  |       1 | A    |          50
2017-12-01 00:00:00 | abc  |       1 | A    |          50
2018-01-01 00:00:00 | abc  |       1 | A    |           0
2018-02-01 00:00:00 | abc  |       1 | A    |           0
2018-03-01 00:00:00 | abc  |       1 | A    |           0
2018-04-01 00:00:00 | abc  |       1 | A    |           0
2018-05-01 00:00:00 | abc  |       1 | A    |           0
2018-06-01 00:00:00 | abc  |       1 | A    |           0
2018-07-01 00:00:00 | abc  |       1 | A    |           0
2018-08-01 00:00:00 | abc  |       1 | A    |           0
2018-09-01 00:00:00 | abc  |       1 | A    |           0
2018-10-01 00:00:00 | abc  |       1 | A    |           0
2018-11-01 00:00:00 | abc  |       1 | A    |           0
2018-12-01 00:00:00 | abc  |       1 | A    |           0
2017-01-01 00:00:00 | abc  |       2 | B    |           0
2017-02-01 00:00:00 | abc  |       2 | B    |           0
2017-03-01 00:00:00 | abc  |       2 | B    |           0
2017-04-01 00:00:00 | abc  |       2 | B    |         100
2017-05-01 00:00:00 | abc  |       2 | B    |         100
2017-06-01 00:00:00 | abc  |       2 | B    |         100
2017-07-01 00:00:00 | abc  |       2 | B    |         100
2017-08-01 00:00:00 | abc  |       2 | B    |         100
2017-09-01 00:00:00 | abc  |       2 | B    |         100
2017-10-01 00:00:00 | abc  |       2 | B    |         100
2017-11-01 00:00:00 | abc  |       2 | B    |         100
2017-12-01 00:00:00 | abc  |       2 | B    |         100
2018-01-01 00:00:00 | abc  |       2 | B    |           0
2018-02-01 00:00:00 | abc  |       2 | B    |           0
2018-03-01 00:00:00 | abc  |       2 | B    |           0
2018-04-01 00:00:00 | abc  |       2 | B    |           0
2018-05-01 00:00:00 | abc  |       2 | B    |           0
2018-06-01 00:00:00 | abc  |       2 | B    |           0
2018-07-01 00:00:00 | abc  |       2 | B    |           0
2018-08-01 00:00:00 | abc  |       2 | B    |           0
2018-09-01 00:00:00 | abc  |       2 | B    |           0
2018-10-01 00:00:00 | abc  |       2 | B    |           0
2018-11-01 00:00:00 | abc  |       2 | B    |           0
2018-12-01 00:00:00 | abc  |       2 | B    |           0
2017-01-01 00:00:00 | xyz  |       1 | A    |           0
2017-02-01 00:00:00 | xyz  |       1 | A    |           0
2017-03-01 00:00:00 | xyz  |       1 | A    |           0
2017-04-01 00:00:00 | xyz  |       1 | A    |           0
2017-05-01 00:00:00 | xyz  |       1 | A    |           0
2017-06-01 00:00:00 | xyz  |       1 | A    |           0
2017-07-01 00:00:00 | xyz  |       1 | A    |           0
2017-08-01 00:00:00 | xyz  |       1 | A    |           0
2017-09-01 00:00:00 | xyz  |       1 | A    |           0
2017-10-01 00:00:00 | xyz  |       1 | A    |           0
2017-11-01 00:00:00 | xyz  |       1 | A    |           0
2017-12-01 00:00:00 | xyz  |       1 | A    |           0
2018-01-01 00:00:00 | xyz  |       1 | A    |           0
2018-02-01 00:00:00 | xyz  |       1 | A    |           0
2018-03-01 00:00:00 | xyz  |       1 | A    |           0
2018-04-01 00:00:00 | xyz  |       1 | A    |           0
2018-05-01 00:00:00 | xyz  |       1 | A    |           0
2018-06-01 00:00:00 | xyz  |       1 | A    |           0
2018-07-01 00:00:00 | xyz  |       1 | A    |           0
2018-08-01 00:00:00 | xyz  |       1 | A    |           0
2018-09-01 00:00:00 | xyz  |       1 | A    |           0
2018-10-01 00:00:00 | xyz  |       1 | A    |           0
2018-11-01 00:00:00 | xyz  |       1 | A    |           0
2018-12-01 00:00:00 | xyz  |       1 | A    |         150

db<>fiddle here

---

Fiscal Years (April to March):

WITH calendar ( month ) AS (
  SELECT ADD_MONTHS( start_month, LEVEL - 1 )
  FROM   (
    SELECT MIN( TRUNC( ADD_MONTHS( month, -3 ), 'YY' ) ) AS start_month,
           ADD_MONTHS( MAX( TRUNC( ADD_MONTHS( month, -3 ), 'YY' ) ), 11 ) AS end_month
    FROM   table_name
  )
  CONNECT BY
          ADD_MONTHS( start_month, LEVEL - 1 ) <= end_month
)
SELECT TO_CHAR( ADD_MONTHS( c.month, 3 ), 'YYYY-MM' ) AS month,
       t.site,
       t.channel,
       t.type,
       SUM( COALESCE( t.revenue, 0 ) ) OVER (
         PARTITION BY t.site, t.channel, t.type, TRUNC( c.month, 'YY' )
         ORDER BY c.month
       ) AS ytd_revenue
FROM   calendar c
       LEFT OUTER JOIN (
         SELECT ADD_MONTHS( month, -3 ) AS month,
                site,
                channel,
                type,
                revenue,
                TRUNC( ADD_MONTHS( month, -3 ), 'YY' ) AS year
         FROM   table_name t
       ) t
       PARTITION BY ( site, channel, type )
       ON ( c.month = t.month )
ORDER BY
       site, channel, type, month;

db<>fiddle here

Answer 2

If I understand correctly, you can use cross join to get all the rows and then left join and a cumulative sum to get the most recent value:

select m.month, sc.site, sc.channel, sc.type,
       sum(revenue) over (partition by sc.site, sc.channel, sc.type, trunc(m.month, 'YYYY') order by m.month) as ytd_revenue
from (select distinct month from t) m cross join
     (select distinct site, channel, type from t) sct left join
     t
     on t.month = m.month and t.site = sct.site and
        t.channel = sc.channel and t.type = sct.type;

This assumes that all months are available in the data. If not, you need to generate the months . . . either with an explicit list or using some sort of generator such as:

with months(month) as (
       select date '2019-01-01' as month
       from dual
       union all
       select month + interval '1' month
       from months
       where month < date '2021-1-01'
     )