## Fastest users to get to 10k

1

(click on this box to dismiss)

In the question at the end, a user mentioned "anybody will get to 10k eventually", which made me wonder. Who was able to get to 10k the quickest? : http://meta.stackexchange.com/questions/365/why-should-10k-rep-users-view-self-deleted-answers

Q&A for professional and enthusiast programmers

``````--get the gain per day per user
WITH UserRepPerDay AS (
SELECT Rep.*, DaysAlive = DATEDIFF(d, U.CreationDate, Rep.CreationDate)
FROM (
SELECT CreationDate, OwnerUserID
, Gain = (
(CASE WHEN SUM(Repgain)>200 THEN 200 ELSE SUM(Repgain) END)
+ SUM(BountyGain)
)
FROM (
SELECT V.VoteTypeId, v.CreationDate, v.BountyAmount, P.OwnerUserId, P.PostTypeId
, RepGain = (
CASE
WHEN VoteTypeId=2 AND PostTypeId=1 Then 5
WHEN VoteTypeId=2 AND PostTypeId=2 Then 10
WHEN VoteTypeId=3 AND PostTypeId=1 Then -2
WHEN VoteTypeId=3 AND PostTypeId=2 Then -2
ELSE 0
END
)
, BountyGain = (
CASE
-- not part of rep cap
WHEN VoteTypeId=1 Then 15
WHEN VoteTypeId=8 Then -BountyAmount
WHEN VoteTypeId=9 Then BountyAmount
ELSE 0
END
)
LEFT JOIN Posts P
ON V.PostId=P.Id
ON P.OwnerUserId=U.Id
WHERE U.Reputation>10000
) List
GROUP BY CreationDate, OwnerUserID
) Rep
ON REP.OwnerUserId=U.Id
WHERE U.Reputation>10000
)
--save it to a temp table so we can add indexes
SELECT *
INTO #UserRepPerDay
FROM UserRepPerDay
--If it took you longer then 200 days, you are not the fastest
--Required to prevent timeouts
WHERE DaysAlive<200

CREATE INDEX IX_UserRepPerDay_OwnerUserId ON #UserRepPerDay (OwnerUserId)
CREATE INDEX IX_UserRepPerDay_DaysAlive ON #UserRepPerDay (DaysAlive)

-- get how much rep the user has on that day
-- again to table to add indexes
SELECT ROW_NUMBER() OVER(ORDER BY OwnerUserId ASC) AS Id, *
INTO #Results
FROM (
SELECT *
, RepTillNow = (
SELECT SUM(Gain)
FROM #UserRepPerDay R2
WHERE R1.OwnerUserId=R2.OwnerUserId
AND R2.DaysAlive<=R1.DaysAlive
)
FROM #UserRepPerDay R1
) Result
WHERE RepTillNow>10000

CREATE INDEX IX_Results_Id ON #Results (Id)
CREATE INDEX IX_Results_RepTillNow ON #Results (RepTillNow)
CREATE INDEX IX_Results_OwnerUserId ON #Results (OwnerUserId)
CREATE INDEX IX_Results_DaysAlive ON #Results (DaysAlive)

--remove duplicate users. Only keep the results with the lowest days alive
DELETE FROM #Results WHERE Id IN (
SELECT R2.Id
FROM #Results R1
LEFT JOIN #Results R2
ON R1.OwnerUserId=R2.OwnerUserId
WHERE R2.DaysAlive>R1.DaysAlive
)

--get the results
SELECT ROW_NUMBER() OVER(ORDER BY DaysAlive ASC) AS Rank, U.Id as [User Link], CAST(R.CreationDate AS nvarchar(12)) as [Got 10k on], R.DaysAlive as [After days], R.RepTillNow as [Rep then], U.Reputation as [Rep now]
FROM Users U
INNER JOIN #Results R
ON U.Id=R.OwnerUserId
ORDER BY DaysAlive``````

### Enter Parameters

Options:
-
Switch to meta site
Hold tight while we fetch your results
:records returned in :time ms:cached