Meta account list

2

Please login or register to vote for this query.

(click on this box to dismiss)

Sports

Q&A for participants in team and individual sport activities

CREATE TABLE #results (site NVARCHAR(250),
                       userid INT,
                       reputation INT,
                       gold INT,
                       silver INT,
                       bronze INT,
                       questions INT,
                       answers INT,
                       virtualmetareputation INT);

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql 
+ IIF( LEN(@sql) > 1 
     , 'UNION ALL'
     , 'INSERT INTO #results'
) +
N'
SELECT * FROM
(SELECT ''' + name + ''' AS Name, u.Id AS UserId, u.Reputation AS Reputation,
  SUM(CASE WHEN b.Class = 1 THEN 1 ELSE 0 END) AS Gold,
  SUM(CASE WHEN b.Class = 2 THEN 1 ELSE 0 END) AS Silver,
  SUM(CASE WHEN b.Class = 3 THEN 1 ELSE 0 END) AS Bronze
  FROM ' + QUOTENAME(name) + '.dbo.Users AS u
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Badges AS b ON b.UserId = u.Id
  WHERE u.AccountId = ' + CAST(##AccountId:int## AS NVARCHAR) + '
  GROUP BY u.Id, u.Reputation
) AS Badges
CROSS JOIN
(SELECT
  SUM(CASE WHEN p.PostTypeId = 1 THEN 1 ELSE 0 END) AS Questions,
  SUM(CASE WHEN p.PostTypeId = 2 THEN 1 ELSE 0 END) AS Answers
  FROM ' + QUOTENAME(name) + '.dbo.Users AS u
  INNER JOIN ' + QUOTENAME(name) + '.dbo.Posts AS p ON p.OwnerUserId = u.Id
  WHERE u.AccountId = ' + CAST(##AccountId:int## AS NVARCHAR) + '
) AS Posts
CROSS JOIN (SELECT NULL AS virtualmetareputation) AS Temp
'
FROM sys.databases
WHERE database_id > 5
  AND name LIKE '%.Meta'
  AND name != 'StackExchange.Meta'
-- 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);

-- Virtual reputation
DECLARE db_c CURSOR FOR SELECT site, userid FROM #results;
DECLARE @Site NVARCHAR(250);
DECLARE @UserId INT;
OPEN db_c;
FETCH NEXT FROM db_c INTO @Site, @UserId;
WHILE(@@FETCH_STATUS = 0)
BEGIN
  SET @sql = N'USE ' + QUOTENAME(@Site) + ';
DECLARE @UserId INT = ' + CAST(@UserId AS NVARCHAR) + ';

UPDATE #results SET virtualmetareputation = (

-- We assume you qualify for the association bonus
SELECT 101 + COALESCE(SUM(Changes), 0) FROM (
SELECT
  SUM(CASE
    WHEN COALESCE(abd.ReputationFromVotes, 0)
       + COALESCE(qbd.ReputationFromVotes, 0)
       + COALESCE(ebd.ReputationFromSuggestedEdits, 0) > 200
    THEN 200
    ELSE COALESCE(abd.ReputationFromVotes, 0)
       + COALESCE(qbd.ReputationFromVotes, 0)
       + COALESCE(ebd.ReputationFromSuggestedEdits, 0)
    END
  + COALESCE(abd.ReputationFromAccepts, 0)) AS Changes
  FROM
  
(SELECT
  v.CreationDate AS VoteDate,

-- Upvotes / downvotes received (downvotes given will be missing)
  SUM(CASE
    WHEN a.OwnerUserId = @UserID
     AND v.VoteTypeId = 2
     AND (a.CommunityOwnedDate > v.CreationDate OR a.CommunityOwnedDate IS NULL)
    THEN 10 
    WHEN a.OwnerUserId = @UserID
     AND v.VoteTypeId = 3
     AND (a.CommunityOwnedDate > v.CreationDate OR a.CommunityOwnedDate IS NULL)
    THEN -2
    ELSE 0 
  END) AS ReputationFromVotes,  

-- Answers accepted
  SUM(CASE
    WHEN q.OwnerUserId = @UserID
     AND a.OwnerUserId != @UserID
     AND v.VoteTypeId = 1
     AND (q.CommunityOwnedDate > v.CreationDate OR q.CommunityOwnedDate IS NULL)
    THEN 2
    WHEN q.OwnerUserId != @UserID
     AND a.OwnerUserId = @UserID
     AND v.VoteTypeId = 1
     AND (a.CommunityOwnedDate > v.CreationDate OR a.CommunityOwnedDate IS NULL)
    THEN 15
    ELSE 0 
  END) AS ReputationFromAccepts

  FROM Posts AS a
  INNER JOIN Posts AS q ON a.ParentId = q.Id 
  INNER JOIN Votes AS v ON v.PostId = a.Id
  WHERE a.OwnerUserId = @UserID
     OR q.OwnerUserId = @UserID

  GROUP BY v.CreationDate
) AS abd

  FULL OUTER JOIN
  
(SELECT
  v.CreationDate AS VoteDate,

-- Upvotes / downvotes received
  SUM(CASE
    WHEN q.OwnerUserId = @UserID
     AND v.VoteTypeId = 2
     AND (q.CommunityOwnedDate > v.CreationDate OR q.CommunityOwnedDate IS NULL)
    THEN 10
    WHEN q.OwnerUserId = @UserID
     AND v.VoteTypeId = 3
     AND (q.CommunityOwnedDate > v.CreationDate OR q.CommunityOwnedDate IS NULL)
    THEN -2
    ELSE 0 
  END) AS ReputationFromVotes

  FROM Posts AS q
  INNER JOIN Votes AS v ON v.PostId = q.Id
  WHERE q.OwnerUserId = @UserID
    AND q.PostTypeId = 1

  GROUP BY v.CreationDate
) AS qbd ON abd.VoteDate = qbd.VoteDate

  FULL OUTER JOIN
  
(SELECT
  DATEADD(DAY, DATEDIFF(DAY, 0, se.ApprovalDate), 0) AS VoteDate,

-- Suggested edit approved
  2 * COUNT(*) AS ReputationFromSuggestedEdits

  FROM SuggestedEdits AS se
  INNER JOIN Posts AS p ON se.PostId = p.Id
  WHERE se.OwnerUserId = @UserID
    AND se.ApprovalDate IS NOT NULL
    AND se.RejectionDate IS NULL
-- This includes edits approved by now-deleted users,
-- but those are extremely hard to filter out.
-- It *ex*cludes edits on deleted posts which qualify for
-- reputation preservation (60 days old, 3+ score)

  GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, se.ApprovalDate), 0)
)

  AS ebd ON abd.VoteDate = ebd.VoteDate
  GROUP BY COALESCE(abd.VoteDate, qbd.VoteDate, ebd.VoteDate)
) AS Temp

) WHERE Site = ''' + @Site + ''';
  
  ';    
  EXEC (@sql);
  FETCH NEXT FROM db_c INTO @Site, @UserId;
END;
CLOSE db_c;
DEALLOCATE db_c;

SELECT 'Total' AS 'Site',
  (SELECT SUM(Reputation) FROM #Results) AS 'Rep (main site)',
  (SELECT SUM(virtualmetareputation) FROM #Results) AS 'Virtual rep (meta)',
  (SELECT SUM(Gold) FROM #Results) AS 'Gold',
  (SELECT SUM(Silver) FROM #Results) AS 'Silver',
  (SELECT SUM(Bronze) FROM #Results) AS 'Bronze',
  (SELECT SUM(COALESCE(questions, 0)) FROM #Results) AS 'Questions',
  (SELECT SUM(COALESCE(answers, 0)) FROM #Results) AS 'Answers'
UNION ALL
SELECT 'Meta Flair', NULL,
  (SELECT SUM(virtualmetareputation) FROM #Results WHERE virtualmetareputation >= 200),
  (SELECT SUM(Gold) FROM #Results WHERE virtualmetareputation >= 200),
  (SELECT SUM(Silver) FROM #Results WHERE virtualmetareputation >= 200),
  (SELECT SUM(Bronze) FROM #Results WHERE virtualmetareputation >= 200),
  NULL, NULL
UNION ALL
SELECT
  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,
  reputation, virtualmetareputation,
  Gold, Silver, Bronze,
  COALESCE(questions, 0), COALESCE(answers, 0)
  FROM #results
  ORDER BY 3 DESC
     
DROP TABLE #results

Enter Parameters

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