Reputation gained by 2019-11-13 change (network-wide)

0

Please login or register to vote for this query.

(click on this box to dismiss)

TeX - LaTeX

Q&A for users of TeX, LaTeX, ConTeXt, and related typesetting systems

CREATE TABLE #results (site NVARCHAR(250),
                       userid INT,
                       reputationgained INT);

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'

SELECT ''' + name + ''', MAX(UserId), SUM(ReputationGained)
  FROM
  -- We have to account for the daily reputation limit here:
  (SELECT UserId,
          (CASE WHEN SUM(NewReputation) > 200 THEN 200 ELSE SUM(NewReputation) END)
        - (CASE WHEN SUM(OldReputation) > 200 THEN 200 ELSE SUM(OldReputation) END)
    AS ReputationGained
    FROM

(SELECT
  u.Id AS UserId,
  v.CreationDate AS Date,

  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,

  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.Users AS u ON p.OwnerUserId = u.Id
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Votes AS v ON v.PostId = p.Id
  WHERE u.AccountId = ' + CAST(##AcountId:int## AS NVARCHAR) + '
    AND v.CreationDate <= ''2019-11-13''
    
  UNION ALL

SELECT
  u.Id AS UserId,
  DATEADD(DAY, DATEDIFF(DAY, 0, ApprovalDate), 0), -- date
  2, -- old reputation
  2  -- new reputation
  FROM SuggestedEdits AS se
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Users AS u ON se.OwnerUserId = u.Id
  WHERE u.AccountId = ' + CAST(##AcountId:int## AS NVARCHAR) + '
    AND se.ApprovalDate IS NOT NULL
    AND se.ApprovalDate <= ''2019-11-13''
) AS Reputation

    GROUP BY UserId, Date) AS Temp
'
FROM sys.databases
WHERE database_id > 5
  AND name NOT LIKE '%_Temp%'
  AND (name NOT LIKE '%.Meta' OR name = 'StackExchange.Meta')
  AND name != 'StackApps' -- that site had already +10 for question upvotes

-- AccountId: The global account ID of the user, which you can find in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID.

EXEC (@sql)

SELECT site,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(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(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      ))
  + '/u/' + CAST(userID AS NVARCHAR) + '|' + Site AS 'Site',
  reputationGained AS 'Reputation gained'
  FROM #results
  WHERE reputationGained > 0
  ORDER BY reputationGained DESC
     
DROP TABLE #results

Enter Parameters

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