1

Please login or register to vote for this query.

(click on this box to dismiss)

Enter EITHER of the site account id, or the network account id.

Q&A for serious players and enthusiasts of chess

DECLARE @AccountId INT;

IF ##SiteAccountId:string?-## <> '-'
  SELECT @AccountId = AccountId
  FROM Users
  WHERE Id = CAST(##SiteAccountId## AS INT);
ELSE
  SELECT @AccountId = CAST(##NetworkAccountId:string?-## AS INT);

PRINT 'Network account id: ' + CAST(@AccountId AS VARCHAR(MAX));

DECLARE @output TABLE (
  DbName VARCHAR(max),
  UserName VARCHAR(max),
  UserId INT,
  Reputation INT,
  GoldenBadges INT,
  SilverBadges INT,
  BronzeBadges INT,
  Questions INT,
  Answers INT,
  Joined DATETIME,
  LastAccessDate DATETIME);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE
  name NOT IN (
    'master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
  AND (
    ##ShowMetaAccounts:int?1## <> 0
    OR
    name NOT LIKE '%.Meta'
  )

DECLARE @dbname VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    WITH PostStat AS (
      SELECT
        SUM(CASE WHEN PostTypeId = 1 THEN 1 ELSE 0 END) AS Questions,
        SUM(CASE WHEN PostTypeId = 2 THEN 1 ELSE 0 END) AS Answers
      FROM
        [' + @dbname + '].dbo.Posts,
        [' + @dbname + '].dbo.Users
      WHERE
        Posts.OwnerUserId = Users.Id
        AND
        Users.AccountId = ' + CAST(@AccountId AS VARCHAR(MAX)) + '
    ),
    BadgeStat AS (
      SELECT
        SUM(CASE WHEN Class = 1 THEN 1 ELSE 0 END) AS Golden,
        SUM(CASE WHEN Class = 2 THEN 1 ELSE 0 END) AS Silver,
        SUM(CASE WHEN Class = 3 THEN 1 ELSE 0 END) AS Bronze
      FROM
        [' + @dbname + '].dbo.Badges,
        [' + @dbname + '].dbo.Users
      WHERE
        Badges.UserId = Users.Id
        AND
        Users.AccountId = ' + CAST(@AccountId AS VARCHAR(MAX)) + '
    )
    SELECT
      ''' + @dbname + ''' AS DbName,
      Users.DisplayName AS UserName,
      Users.Id AS UserId,
      Users.Reputation AS Reputation,
      BadgeStat.Golden AS GoldenBadges,
      BadgeStat.Silver AS SilverBadges,
      BadgeStat.Bronze AS BronzeBadges,
      PostStat.Questions AS Questions,
      PostStat.Answers AS Answers,
      Users.CreationDate AS Joined,
      Users.LastAccessDate AS LastAccessDate
    FROM [' + @dbname + '].dbo.Users, PostStat, BadgeStat
    WHERE AccountId = ' + CAST(@AccountId AS VARCHAR(MAX)) + ';'
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH SiteDb AS (
  SELECT
    name AS DbName,
    LOWER(CASE
      WHEN
        name = 'StackExchange.Programmers'
      THEN
        'softwareengineering.stackexchange.com'
      WHEN
        name = 'StackExchange.Programmers.Meta'
      THEN
        'softwareengineering.meta.stackexchange.com'
      WHEN
        name LIKE 'StackExchange.%'
      THEN
        RIGHT(name, LEN(name) - 14) + '.stackexchange.com'
      WHEN
        name = 'StackOverflow.Br'
      THEN
        'pt.stackoverflow.com'
      WHEN
        name = 'StackOverflow.Br.Meta'
      THEN
        'pt.meta.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__'
      THEN
        RIGHT(name, 2) + '.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__.Meta'
      THEN
        RIGHT(name, 7) + '.stackoverflow.com'
      WHEN
        name = 'StackOverflow'
      THEN
        'stackoverflow.com'
      WHEN
        name = 'StackOverflow.Meta'
      THEN
        'meta.stackoverflow.com'
      WHEN
        name = 'StackApps'
      THEN
        'stackapps.com'
      WHEN
        name = 'ServerFault'
      THEN
        'serverfault.com'
      WHEN
        name = 'ServerFault.Meta'
      THEN
        'meta.serverfault.com'
      WHEN
        name = 'SuperUser'
      THEN
        'superuser.com'
      WHEN
        name = 'SuperUser.Meta'
      THEN
        'meta.superuser.com'
      ELSE
        NULL
      END
    ) AS SiteName
  FROM sysdatabases
  WHERE
    name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
)
SELECT
  DENSE_RANK() OVER
    (ORDER BY Output.Reputation DESC) AS Rank,
  SiteDb.SiteName,
  Output.UserName,
  Output.Reputation,
  Output.UserId,
  'https://' + SiteDb.SiteName + '/users/' +
    CAST(Output.UserId AS VARCHAR(MAX)) + '?tab=topactivity|Profile'
    AS Profile,
  Output.GoldenBadges,
  Output.SilverBadges,
  Output.BronzeBadges,
  Output.Questions,
  Output.Answers,
  Output.Joined,
  Output.LastAccessDate
FROM @output AS Output, SiteDb
WHERE Output.DbName = SiteDb.DbName
ORDER BY Output.Reputation DESC, SiteDb.DbName;

SELECT SUM(Reputation) AS SummedRep
FROM @output Output
WHERE DbName NOT LIKE '%.Meta' OR DbName = 'StackExchange.Meta';

fork query

edited feb 10 21 peterh (2)

created may 31 17 peterh