List of communities of an user, included the hidden ones

2

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

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);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange');

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 = ##AccountId:INT##
    ),
    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 = ##AccountId:INT##
    )
    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
    FROM [' + @dbname + '].dbo.Users, PostStat, BadgeStat
    WHERE AccountId = ##AccountId##';
  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
FROM @output AS Output, SiteDb
WHERE Output.DbName = SiteDb.DbName
ORDER BY Output.Reputation DESC, SiteDb.DbName;

Enter Parameters

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