I love Itzik Ben-Gan's books, very valuable in practical life.
WITH CTE (Rank_Number,row_date,PBXACD_ID,split,center_id) AS
(SELECT
RANK() OVER ( PARTITION BY row_date, PBXACD_ID, split ORDER BY COUNT(*) DESC) AS Rank_Number,
row_date,
PBXACD_ID,
split,
center_id
FROM
TestDB.dbo.TestTable (NOLOCK)
WHERE
row_date >= CAST(DATEADD(day, -3,...