I want the earliest 5 distinct dates for each user.
I know I can row_number() over partition by user order by date, but there are a lot more than 3 users and a lot more than 12 dates.
I'm having a moment here, and I'm pretty there's a much simpler solution.
This will generate unordered test data.
IF OBJECT_ID('tempdb..#tmp_UserDates') IS NOT NULL
DROP TABLE #tmp_UserDates
dDate = CAST(DATEADD(DAY, t.n, DATEADD(MILLISECOND, ABS(CHECKSUM(NEWID())), '1 Jan 2021')) AS DATE)
FROM (SELECT cUser = 'User1'
UNION SELECT 'User2'
UNION SELECT 'User3') AS Users
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS t(n)
ORDER BY NEWID()