Rep-cap streaks

1

Please login or register to vote for this query.

(click on this box to dismiss)

Finds the longest streak of rep-cap days (days in which the user earned 200 or more rep) for all users over a given reputation threshold. Based on https://data.stackexchange.com/stackoverflow/query/708497/epic-legendary-progress-progress-graph

Stack Overflow

Q&A for professional and enthusiast programmers

WITH 
RepChangeTable (UserId, RepChange, Date, UserRep) AS (
  SELECT
    Posts.OwnerUserId AS UserId,
    CASE VoteTypeId
         WHEN 1 THEN 15
         WHEN 2 THEN 10
         WHEN 3 THEN -2
         WHEN 8 THEN -BountyAmount
         WHEN 9 THEN BountyAmount
         WHEN 16 THEN 2
         END RepChange,
    Votes.CreationDate AS Date,
    Users.Reputation AS UserRep
  FROM Votes
  INNER JOIN Posts ON Posts.Id = Votes.PostId
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
  WHERE VoteTypeId IN (1,2,3,8,9,16)
    AND Posts.CommunityOwnedDate IS NULL
  ),

RepChangeTotals (UserId, Date, DailyRep, UserRep) AS (
  SELECT
    UserId AS [UserId],
    Date AS [Date],
    SUM(RepChange) AS [DailyRep],
    UserRep AS [UserRep]
  FROM RepChangeTable
  --WHERE UserId IN (1325,8563)
  WHERE UserRep >= ##MinRep?10000##
  GROUP BY UserId, Date, UserRep
  ),

RepCapDays (UserId, Date, Number) AS (
  SELECT 
    UserId,
    Date as [Date],
    ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date) AS [Number]
  FROM RepChangeTotals
  WHERE DailyRep >= 200
  ),

RepCapStreaks (UserId, StreakStartDate, StreakEndDate, StreakLength) AS (
  SELECT
    StartDays.UserId,
    StartDays.Date AS StreakStartDate,
    EndDays.Date AS StreakEndDate,
    EndDays.Number - StartDays.Number + 1 AS StreakLength
  FROM RepCapDays StartDays
  JOIN RepCapDays EndDays ON StartDays.UserId = EndDays.UserId
  WHERE EndDays.Number > StartDays.Number
    AND EndDays.Number-StartDays.Number = DATEDIFF(day, StartDays.Date, EndDays.Date)
    )
    
    
SELECT
  UserId AS [User Link],
  StreakStartDate,
  StreakEndDate,
  StreakLength
FROM RepCapStreaks
WHERE StreakLength = (
         SELECT MAX(StreakLength) 
         FROM RepCapStreaks ref
         WHERE ref.UserId = RepCapStreaks.UserId
         )
ORDER BY StreakLength DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results