Moving average trading strategy
Question
Suppose you're given the following table showing open, high, low, and close prices for a stock on a given date (preview of the schema, full schema/table in SQL fiddle link below):
Table: Prices
| Date | Open | High | Low | Close |
|---|---|---|---|---|
| 2020-01-02 00:00:00 | 1875 | 1898.01 | 1864.15 | 1898.01 |
| 2020-01-03 00:00:00 | 1864.5 | 1886.2 | 1864.5 | 1874.97 |
| 2020-01-06 00:00:00 | 1860 | 1903.69 | 1860 | 1902.88 |
| 2020-01-07 00:00:00 | 1904.5 | 1913.89 | 1892.04 | 1906.86 |
Given this data, you're asked to apply the following trading strategy:
- Calculate the 10-day moving average of the closing price
- Then, if the price of the day's open is greater than the 10-day moving average, you will "buy" the stock on open and sell on close for that day.
Write a SQL query to apply this trading strategy and return the total earnings/loss over the period of data provided.
You can view/query the table in an interactive SQL fiddle here.