Tallying up closing prices above the 3-day moving average in SQL
Question
Suppose you're given data showing daily closing prices for a couple of different stock tickers (as shown in the bottom of the question). Using this table, write a SQL query to calculate the 3-day moving average for each ticker, then return the number of days each ticker is above their respective 3-day moving average.
Table: stock_prices
| ticker | date | closing_price |
|---|---|---|
| ABC | 2021-01-31 | 52.01 |
| ABC | 2021-02-01 | 53.4 |
| ABC | 2021-02-02 | 55.23 |
| ABC | 2021-02-03 | 54.2 |
| ABC | 2021-02-04 | 56.2 |
| ABC | 2021-02-05 | 55.12 |
| ABC | 2021-02-06 | 55.88 |
| ABC | 2021-02-07 | 55.5 |
| ABC | 2021-02-08 | 59.33 |
| ABC | 2021-02-09 | 59.12 |
| ABC | 2021-02-10 | 53.09 |
| XYZ | 2021-01-31 | 41.06 |
| XYZ | 2021-02-01 | 43.05 |
| XYZ | 2021-02-02 | 42.4 |
| XYZ | 2021-02-03 | 46.01 |
| XYZ | 2021-02-04 | 43.87 |
| XYZ | 2021-02-05 | 44.28 |
| XYZ | 2021-02-06 | 46.44 |
| XYZ | 2021-02-07 | 47.96 |
| XYZ | 2021-02-08 | 44.69 |
| XYZ | 2021-02-09 | 48.01 |
| XYZ | 2021-02-10 | 48 |
Click here to view these tables in an interactive SQL fiddle.