Q&A about the site for music historians, critics, and fans
declare @sql nvarchar(max) create table #result (site sysname , hostname sysname , displayname nvarchar(150) , id integer , accountid integer , downvotes integer , upvotes integer , vtc integer ) select @sql = N'insert into #result' + STRING_AGG(concat(N' select ''', name ,N''' as site , ''',hostname ,N''' , u.displayname , u.id , u.accountid , sum(case v.votetypeid when 3 then 1 else 0 end) downmod , sum(case v.votetypeid when 2 then 1 else 0 end) upmod , sum(case v.votetypeid when 6 then 1 else 0 end) vtc from ', quotename(name), N'.dbo.users u left outer join ', quotename(name), N'.dbo.posts p on p.owneruserid = u.id left outer join ', quotename(name), N'.dbo.votes v on v.postid = p.id where u.id > 0 and v.votetypeid in (2,3,6) group by u.displayname, u.id, u.accountid '),N' union all') from (select convert(nvarchar(max),name) name -- , convert(nvarchar(max),sitename) sitename -- , meta -- , domain , concat( -- based on an idea from Glorfindel -- https://meta.stackexchange.com/a/321181 (case sitename WHEN 'Audio' THEN 'video' WHEN 'Beer' THEN 'alcohol' WHEN 'CogSci' THEN 'psychology' WHEN 'Garage' THEN 'mechanics' WHEN 'Health' THEN 'medicalsciences' WHEN 'Moderators' THEN 'communitybuilding' WHEN 'Photography' THEN 'photo' WHEN 'Programmers' THEN 'softwareengineering' WHEN 'Vegetarian' THEN 'vegetarianism' WHEN 'Writers' THEN 'writing' WHEN 'Br' THEN 'pt' WHEN 'Mathoverflow' THEN concat((meta+'.'), sitename) ELSE case when sitename = domain then null else sitename end end +'.') , (case when sitename <> 'mathoverflow' then (meta+'.') else null end) , (case when sitename <> 'mathoverflow' then concat((domain + '.'), 'com') else 'net' end) ) hostname from ( select name , case parsename(name,1) when 'Meta' then parsename(name,2) else parsename(name,1) end [sitename] , case parsename(name,1) when 'Meta' then 'meta' else null end [meta] , coalesce(parsename(name,3), parsename(name,2)) [domain] from sys.databases where database_id > 5 and (name not like '%.Meta' or name = 'StackExchange.Meta') ) dbs ) dbsall exec (@sql) select accountid , concat('https://stackexchange.com/users/' , accountid , '|' , min(displayname) ) as [Network profile] , count(*) as [sites] , sum(downvotes) [sum of downvotes] , sum(upvotes) [sum of upvotes] , IIF((sum(downvotes)+sum(upvotes)) != 0, 1.0*sum(downvotes)/(sum(downvotes)+sum(upvotes))*100, 0.0) [downvote ratio (%)] , sum(vtc) [vtc] from #result where accountid IN ('8303225') group by accountid order by (IIF(sum(upvotes) > 100, 1.0*sum(downvotes)/(sum(downvotes)+sum(upvotes)), 0.0)) desc