0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

-- from https://meta.stackexchange.com/a/316250/295232
CREATE TABLE #mods (site nvarchar(50), userid int, username nvarchar(50));
INSERT INTO #mods VALUES ('stackoverflow.com', 13531, 'Michael Myers'), ('stackoverflow.com', 19679, 'Brad Larson'), ('stackoverflow.com', 59303, 'ChrisF'), ('stackoverflow.com', 168175, 'Flexo'), ('stackoverflow.com', 707111, 'Ry-'), ('stackoverflow.com', 256196, 'Bohemian'), ('stackoverflow.com', 229044, 'user229044'), ('stackoverflow.com', 100297, 'Martijn Pieters'), ('stackoverflow.com', 1252759, 'Jon Clements'), ('stackoverflow.com', 3093387, 'josliber'), ('stackoverflow.com', 1849664, 'Undo'), ('stackoverflow.com', 476, 'deceze'), ('stackoverflow.com', 541136, 'Russia Must Remove Putin'), ('stackoverflow.com', 189134, 'Andy'), ('stackoverflow.com', 366904, 'Cody Gray'), ('stackoverflow.com', 584192, 'Samuel Liew'), ('stackoverflow.com', 563532, 'Rob'), ('stackoverflow.com', 3002139, 'Baum mit Augen'), ('stackoverflow.com', 6451573, 'Jean-François Fabre'), ('stackoverflow.com', 3773011, 'Makyen'), ('stackoverflow.com', 2370483, 'Machavity'), ('stackoverflow.com', 7311767, 'Stephen Rauch'), ('stackoverflow.com', 6296561, 'Zoe stands with Ukraine'), ('stackoverflow.com', 1839439, 'Dharman'), ('stackoverflow.com', 208273, 'Ryan M');

-- Site details, cf. https://meta.stackexchange.com/a/359795/295232
CREATE TABLE #Sites (URL NVARCHAR(100), SiteName NVARCHAR(100), DatabaseName NVARCHAR(50));
-- fetch Markdown for post containing table with site details
DECLARE @md NVARCHAR(MAX);
SELECT TOP 1 @md = Text
  FROM [StackExchange.Meta].dbo.PostHistory
  WHERE PostId = 359796 AND PostHistoryTypeId = 5
  ORDER BY CreationDate DESC;
-- split Markdown on pipe symbol
CREATE TABLE #TableEntries (ID INT IDENTITY(1, 1), Value NVARCHAR(100));
INSERT INTO #TableEntries (Value)
SELECT LEFT(TRIM(VALUE), 100) FROM STRING_SPLIT(@md, '|')
  WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0;
-- 'pivot' - we have to discard the introductory text, headers and horizontal lines
INSERT INTO #Sites
SELECT t0.Value, t1.Value, t2.Value
  FROM #TableEntries AS t0
  INNER JOIN #TableEntries AS t1
    ON t0.Id + 1 = t1.Id
  INNER JOIN #TableEntries AS t2
    ON t0.Id + 2 = t2.Id
  WHERE t0.Id >= 8 -- that's where the real content starts
    AND t0.Id % 3 = 2;

-- add database name to #mods table
ALTER TABLE #mods ADD [database] NVARCHAR(50);
UPDATE #mods
  SET #mods.[database] = #sites.DatabaseName
  FROM #Sites WHERE #Sites.URL = 'https://' + #mods.site;

-- fetch reputation + bounties
CREATE TABLE #accounts (site NVARCHAR(50), userid INT, displayname NVARCHAR(40), reputation INT, bounties INT);
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + IIF(LEN(@sql) > 1, 'UNION', 'INSERT INTO #accounts'
) + '
SELECT ''' + site + ''', ' + CAST(userid AS NVARCHAR) + ', u.DisplayName, u.Reputation, SUM(v.BountyAmount)
  FROM ' + QUOTENAME([database]) + '.dbo.Users AS u
  LEFT OUTER JOIN ' + QUOTENAME([database]) + '.dbo.Votes AS v
    ON v.UserId = u.Id AND v.VoteTypeId = 8 -- Bounty Start
  WHERE u.Id = ' + CAST(userid AS NVARCHAR) + '
  GROUP BY u.DisplayName, u.Reputation
' FROM #mods;
EXEC(@sql);

SELECT site AS 'Site',
  'https://' + site + '/users/' + CAST(userid AS NVARCHAR) + '|' + displayname AS 'Moderator',
  reputation AS 'Reputation',
  bounties AS 'Bounties given'
  FROM #accounts ORDER BY reputation;

DROP TABLE #accounts;

fork query

edited aug 12 anonymous

created aug 7 19 Glorfindel