Nice, concise, easily-readable article.
However, I'd change the key query because it reads the prices table twice. This reads it once:
;WITH PriceYearly_CTE2 AS
(SELECT
Period = ROW_NUMBER() OVER (ORDER BY [year]),
ticker,
close_,
[year]
FROM (
SELECT
MyRow = ROW_NUMBER() OVER(PARTITION BY YEAR(date_) ORDER BY date_ DESC),
ticker,
close_,
YEAR(date_) AS [year]
FROM prices
) d
WHERE MyRow = 1
)
SELECT * FROM PriceYearly_CTE2
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden