Q&A for those who study, teach, research and apply economics and econometrics
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;