Which sites changed their highest-rep user as a result of the recent question rep recalc?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

CREATE TABLE #results (site NVARCHAR(250),
                       userid INT,
                       displayname NVARCHAR(40),
                       newreputation INT,
                       newrank INT,
                       oldreputation INT,
                       oldrank INT);

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT ''' + name + ''', UserId, DisplayName, Reputation,
  RANK() OVER (ORDER BY Reputation DESC),
  Reputation -
       SUM((CASE WHEN NewReputation > 200 THEN 200 ELSE NewReputation END)
         - (CASE WHEN OldReputation > 200 THEN 200 ELSE OldReputation END)),
  RANK() OVER (ORDER BY Reputation -
       SUM((CASE WHEN NewReputation > 200 THEN 200 ELSE NewReputation END)
         - (CASE WHEN OldReputation > 200 THEN 200 ELSE OldReputation END)) DESC)
  FROM
  (SELECT
    p.OwnerUserId AS UserId,
    v.CreationDate AS VoteDate,

  SUM(CASE
    WHEN p.PostTypeId = 1 -- question
     AND v.VoteTypeId = 2 -- upvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN 5
    WHEN p.PostTypeId = 2 -- answer
     AND v.VoteTypeId = 2 -- upvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN 10
    WHEN v.VoteTypeId = 3 -- downvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN -2
    ELSE 0 
  END) AS OldReputation,

  SUM(CASE
    WHEN v.VoteTypeId = 2 -- upvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN 10 
    WHEN v.VoteTypeId = 3 -- downvote
     AND (p.CommunityOwnedDate > v.CreationDate OR p.CommunityOwnedDate IS NULL)
    THEN -2
    ELSE 0 
  END) AS NewReputation
  
    FROM ' + QUOTENAME(name) + '.dbo.Posts AS p
    INNER JOIN ' + QUOTENAME(name) + '.dbo.Votes AS v ON v.PostId = p.Id
    WHERE p.OwnerUserId IN (SELECT TOP 10 Id FROM ' + QUOTENAME(name) + '.dbo.Users ORDER BY Reputation DESC)

    GROUP BY p.OwnerUserId, v.CreationDate
  ) AS ReputationByDate
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Users ON ReputationByDate.UserId = Users.Id
  GROUP BY UserId, DisplayName, Reputation
'
FROM sys.databases
WHERE database_id > 5
  AND (name NOT LIKE '%.Meta' OR name = 'StackExchange.Meta')
  AND name != 'StackApps' -- that site had already +10 for question upvotes

-- execute it
EXEC (@sql)

SELECT n.site,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', n.site) > 0,
    IIF(PATINDEX('%.Meta', n.site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', n.site) > 0,
      IIF(PATINDEX('%.Meta', n.site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', n.site) > 0,
          CASE SUBSTRING(n.site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(n.site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', n.site) > 0,
            CASE SUBSTRING(n.site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(n.site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', n.site) > 0,
              'meta.' + SUBSTRING(n.site, 0, PATINDEX('%.Meta', n.site)) + '.com',
              n.site + '.com'
            )
          )
        )
      )
      ))
  + '/u/' + CAST(n.userID AS NVARCHAR) + '|' + n.DisplayName AS 'New top user',
  n.oldReputation AS 'From',
  n.newReputation AS 'To',
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', o.site) > 0,
    IIF(PATINDEX('%.Meta', o.site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', o.site) > 0,
      IIF(PATINDEX('%.Meta', o.site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', o.site) > 0,
          CASE SUBSTRING(o.site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(o.site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', o.site) > 0,
            CASE SUBSTRING(o.site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(o.site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', o.site) > 0,
              'meta.' + SUBSTRING(o.site, 0, PATINDEX('%.Meta', o.site)) + '.com',
              o.site + '.com'
            )
          )
        )
      )
      ))
  + '/u/' + CAST(o.userID AS NVARCHAR) + '|' + o.DisplayName AS 'Old top user',
  o.oldReputation AS 'From',
  o.newReputation AS 'To'
  FROM #results AS n
  INNER JOIN #results AS o
    ON n.site = o.site
   AND n.newRank = 1
   AND o.oldRank = 1
   AND n.userID != o.userID
     
DROP TABLE #results

Enter Parameters

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