'Group by and sum multiple columns
I have data in multiple columns, and I'm trying to find day-over-day growth rates across columns. I know this can be done with a simple window function. However, I'm also trying to figure out growth rates for particular column variables with 'ALL' in the remaining columns.
| Date | Region | Territory | Value |
|---|---|---|---|
| 1/1/2022 | US | North | 10 |
| 1/1/2022 | EU | West | 11 |
| 1/1/2022 | US | South | 13 |
| 1/1/2022 | EU | East | 14 |
| 1/2/2022 | US | North | 20 |
| 1/2/2022 | EU | West | 33 |
| 1/2/2022 | US | South | 26 |
| 1/2/2022 | EU | East | 42 |
Expected output is:
| Date | Region | Territory | Growth Rate |
|---|---|---|---|
| 1/2/2022 | US | ALL | 100% |
| 1/2/2022 | EU | ALL | 200% |
| 1/2/2022 | ALL | North | 100% |
| 1/2/2022 | ALL | East | 200% |
| 1/2/2022 | ALL | West | 100% |
| 1/2/2022 | ALL | South | 200% |
| 1/2/2022 | US | North | 100% |
| 1/2/2022 | US | South | 100% |
| 1/2/2022 | EU | East | 200% |
| 1/2/2022 | EU | West | 200% |
Edit: I'm building this out in Athena which uses Presto SQL.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|