Flagging increasing stock prices in SQL
Question
Suppose you're given the following table showing closing prices for a stock, cut by date:
Table: Prices
| Date | ClosingPrice |
|---|---|
| 2020-01-31 00:00:00 | 52.01 |
| 2020-02-01 00:00:00 | 53.4 |
| 2020-02-02 00:00:00 | 55.23 |
| 2020-02-03 00:00:00 | 54.2 |
| 2020-02-04 00:00:00 | 56.2 |
| 2020-02-05 00:00:00 | 55.12 |
| 2020-02-06 00:00:00 | 55.88 |
| 2020-02-07 00:00:00 | 55.5 |
| 2020-02-08 00:00:00 | 59.33 |
| 2020-02-09 00:00:00 | 59.12 |
| 2020-02-10 00:00:00 | 53.09 |
Using this table, write a SQL query to calculate 3 and 5-day moving averages. Then, add 2 more columns to flag when a given closing price is over the 3 or 5-day moving average.
You can view/query the table in an interactive SQL editor here.