Partitioned Running Totals
Question:
The stocks must be ordered first by sector and second by name. They are about to enter our portfolio one by one. When all the stocks from the same sector have entered then passive investment starts and the portfolio takes off. We would like to know what the running total dividend of the stocks is.
Return: name, sector, running total dividend
Order by: sector, name
Dividends:
| stock | varchar |
| sector | varchar |
| dividend | float |
| frequency | varchar |
| growth | float |
| safety | int |
Desired output:
| name | sector | running_total_dividend |
| American Express | Financial | 1.19 |
| Bank of America | Financial | 3.73 |
| JPMorgan Chase | Financial | 6.13 |
| Visa | Financial | 6.90 |
| Johnson & Johnson | Healthcare | 3.32 |
| UnitedHealth | Healthcare | 4.85 |
| Realty Income | Services | 5.56 |
| Starbucks | Services | 8.68 |
| VICI Properties | Services | 14.44 |
| Apple | Technology | 0.55 |
| Microsoft | Technology | 1.28 |
| Texas Instruments | Technology | 4.19 |
Query Window:
Correct output but can you use 'partition by'?
Good work!
Over coupled with partition by allows us to further break down our aggregate functions,
it is often used for running totals of types or classes of items.
next question
Answer:
select name, sector,
sum(dividend) over (partition by sector order by name) as running_total_dividend
from dividends