Examining nearby rows
Question:
To analyze stocks effectively, we need to calculate the average growth rate of each stock, along with the stock just after it and the stock just before it. The first and last stocks are intended to have an average growth rate of 2 stocks, not 3.
Return: name, growth, average_dividend_growth
Order by: growth
Show Table
Dividends:
| stock | varchar |
| sector | varchar |
| dividend | float |
| frequency | varchar |
| growth | float |
| safety | int |
Show Desired Output
Desired output:
| name | growth | average_dividend_growth |
| Realty Income | 2.98 | 3.49 |
| JPMorgan Chase | 4.00 | 4.23 |
| Johnson & Johnson | 5.70 | 5.15 |
| Apple | 5.74 | 6.65 |
| Bank of America | 8.51 | 7.64 |
| VICI Properties | 8.66 | 8.70 |
| Starbucks | 8.93 | 9.36 |
| American Express | 10.49 | 9.98 |
| Texas Instruments | 10.51 | 10.66 |
| Microsoft | 10.98 | 11.99 |
| Visa | 14.47 | 13.39 |
| UnitedHealth | 14.71 | 14.59 |
Query Window:
Correct output but can you use 'precedingfollowing'?
Show Answer
Answer:
select name, growth,
avg(growth) over (order by growth ROWS between 1 preceding and 1 following) as average_dividend_growth
from dividends order by growth