Fastest users to get to 10k

1

Please login or register to vote for this query.

(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

Stack Overflow

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
        )
      FROM Votes V
      LEFT JOIN Posts P
      ON V.PostId=P.Id
      LEFT JOIN Users U
      ON P.OwnerUserId=U.Id
      WHERE U.Reputation>10000
    ) List
    GROUP BY CreationDate, OwnerUserID
  ) Rep
  LEFT JOIN Users U
  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
loading Hold tight while we fetch your results