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.
Advertisement
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