Oracle SQL revenue YTD computation

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

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