2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250)
                       , accountid int
                       , displayname nvarchar(40)
                       , badgecount int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , u.accountid
     , u.displayname
     , count(*)
from ' + quotename(name) + '.dbo.badges b
inner join ' + quotename(name) + '.dbo.users u on b.userid = u.id
where b.name = ''' + ##badgename:string## + '''
group by u.accountid, u.displayname '
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

-- execute it
exec (@sql)

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

select rank() over (order by count(*) desc) as 'Rank',
  'https://stackexchange.com/users/' + CAST(accountid AS nvarchar)
  + '|' + MAX(displayname) AS 'User',
  sum(badgecount) AS 'Total badges',
  count(*) AS 'Total sites',
  string_agg(cast(SiteName as nvarchar(max)), ', ') AS 'Sites'
  FROM #results AS r
  INNER JOIN #Sites AS s
    ON r.Site = s.DatabaseName
  group by accountid
  order by count(*) desc, sum(badgecount) desc

drop table #results

fork query

edited jan 24 21

created oct 29 17 Glorfindel