Calculating returns within SQL query

I have data on stock prices for multiple companies for the last 10 years. I want to be able to query this table to return the annual (calendar year) stock price return for each of these stocks. Note that the same dates for each stock may not exist, so I am trying to dynamically calculate the return using the earliest and latest available date for each stock.

My table looks like this:

Date       | Stock    | Price
========== | ======== | =====
2018-01-03 | AAPL     | 200
2018-04-20 | AAPL     | 210
2018-07-10 | AAPL     | 230
2018-10-05 | AAPL     | 250
2018-12-20 | AAPL     | 290
2019-01-06 | AAPL     | 300
2019-06-15 | AAPL     | 280
2019-09-10 | AAPL     | 340
2019-12-28 | AAPL     | 400
2018-01-02 | MSFT     | 80
2018-04-20 | MSFT     | 90
2018-07-10 | MSFT     | 110
2018-10-05 | MSFT     | 100
2018-12-22 | MSFT     | 95
2019-01-10 | MSFT     | 110
2019-04-20 | MSFT     | 105
2019-06-19 | MSFT     | 120
2019-09-11 | MSFT     | 140
2019-12-30 | MSFT     | 150

I am looking to grab the earliest and latest stock price for each stock, as follows:

Date       | Stock    | Price
========== | ======== | =====
2018-01-03 | AAPL     | 200
2018-12-20 | AAPL     | 290
2019-01-06 | AAPL     | 300
2019-12-28 | AAPL     | 400
2018-01-02 | MSFT     | 80
2018-12-22 | MSFT     | 95
2019-01-10 | MSFT     | 110
2019-12-30 | MSFT     | 150

And finally, I am trying to calculate the return (End of year price / Start of year price – 1)

Year  | Stock    | Return
===== | ======== | =====
2018  | AAPL     | 0.45
2019  | AAPL     | 0.3333
2018  | MSFT     | 0.1875
2019  | MSFT     | 0.3636

What would be the most efficient way to achieve this result (as I will be running this on over 1000 stocks in a 10 year period, which is likely to get computationally intensive)?

Answer

It shouldn’t be too bad. I have built this query based on your sample (plus a single row for 2017):

DECLARE @stocks TABLE (
    Date    DATETIME,
    Stock   VARCHAR(10),
    Price   MONEY
)

INSERT INTO  @stocks ( Date, Stock, Price )
VALUES
(' 2017-01-03' , 'AAPL', 200),
(' 2018-01-03' , 'AAPL', 200),
(' 2018-04-20' , 'AAPL', 210),
(' 2018-07-10' , 'AAPL', 230),
(' 2018-10-05' , 'AAPL', 250),
(' 2018-12-20' , 'AAPL', 290),
(' 2019-01-06' , 'AAPL', 300),
(' 2019-06-15' , 'AAPL', 280),
(' 2019-09-10' , 'AAPL', 340),
(' 2019-12-28' , 'AAPL', 400),
(' 2018-01-02' , 'MSFT', 80 ),
(' 2018-04-20' , 'MSFT', 90 ),
(' 2018-07-10' , 'MSFT', 110),
(' 2018-10-05' , 'MSFT', 100),
(' 2018-12-22' , 'MSFT', 95 ),
(' 2019-01-10' , 'MSFT', 110),
(' 2019-04-20' , 'MSFT', 105),
(' 2019-06-19' , 'MSFT', 120),
(' 2019-09-11' , 'MSFT', 140),
(' 2019-12-30' , 'MSFT', 150)

SELECT S1.Stock, S1.MinDate, S2.Price, S1.MaxDate, S3.Price
, (S3.Price / S2.Price) - 1 AS 'Return'
FROM (
    SELECT Stock, MIN(date) AS MinDate, MAX(date) AS MaxDate FROM @stocks GROUP BY Stock, YEAR(date)
) AS S1

LEFT JOIN @stocks AS S2
ON S2.Stock = S1.Stock
AND S2.Date = S1.MinDate

LEFT JOIN @stocks AS S3
ON S3.Stock = S1.Stock
AND S3.Date = S1.MaxDate

ORDER BY S1.Stock, YEAR(S1.MinDate)