SELECT UserName, Drink, Value, CreatedDate
FROM ( -- d
SELECT UserName, Drink, Value, CreatedDate,
rn = ROW_NUMBER() OVER(PARTITION BY grp ORDER BY CreatedDate DESC)
FROM #TEMP t
OUTER APPLY ( -- ou
SELECT TOP 1 grp
FROM (SELECT UserName, CreatedDate, grp = ROW_NUMBER() OVER(ORDER BY CreatedDate) FROM #TEMP WHERE Drink = 'HardDrink') ti
WHERE ti.UserName = t.UserName
AND ti.CreatedDate > t.CreatedDate
AND t.Drink <> 'HardDrink'
ORDER BY ti.CreatedDate ASC
) ou
WHERE (t.Drink = 'Pepsi' AND grp > 0)
OR (t.Drink IN ('Fanta','Coke') AND grp IS NULL)
) d
WHERE rn = 1
ORDER BY UserName, CreatedDate
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