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.

Solution

Access restricted

Subscribe to premium account to see the solution.