Moving average trading strategy


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.


Access restricted

Subscribe to premium account to see the solution.

Get premium now