Q&A for professionals, academics, and students working within the systems development life cycle
CREATE TABLE #results (site NVARCHAR(250), newtopuserid INT, oldtopuserid INT); DECLARE @sql NVARCHAR(MAX) = ''; SELECT @sql = @sql + IIF( LEN(@sql) > 1 , 'UNION ALL' , 'INSERT INTO #results' ) + N' SELECT ''' + name + ''', (SELECT TOP 1 Id FROM ' + QUOTENAME(name) + '.dbo.Users ORDER BY Reputation DESC), (SELECT TOP 1 UserId FROM ( SELECT p.OwnerUserId AS UserId, v.CreationDate AS VoteDate, SUM(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, SUM(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.Votes AS v ON v.PostId = p.Id WHERE p.OwnerUserId IN (SELECT TOP 10 Id FROM ' + QUOTENAME(name) + '.dbo.Users ORDER BY Reputation DESC) GROUP BY p.OwnerUserId, v.CreationDate ) AS ReputationByDate INNER JOIN ' + QUOTENAME(name) + '.dbo.Users ON ReputationByDate.UserId = Users.Id GROUP BY UserId, Reputation ORDER BY Reputation - SUM((CASE WHEN NewReputation > 200 THEN 200 ELSE NewReputation END) - (CASE WHEN OldReputation > 200 THEN 200 ELSE OldReputation END)) DESC) ' FROM sys.databases WHERE database_id > 5 AND (name NOT LIKE '%.Meta' OR name = 'StackExchange.Meta') -- execute it 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(newtopuserid AS NVARCHAR) AS 'New top user', 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(oldtopuserid AS NVARCHAR) AS 'Old top user' FROM #results WHERE newtopuserid != oldtopuserid DROP TABLE #results