users who significantly contributed to the network but still have very high downvote ratio
Q&A for peer programmer code reviews
declare @sql nvarchar(max) create table #result (site sysname , hostname sysname , displayname nvarchar(150) , id integer , accountid integer , downvotes integer , upvotes 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 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) 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 top 100 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 (%)] from #result group by accountid order by (IIF(sum(upvotes) > 100, 1.0*sum(downvotes)/(sum(downvotes)+sum(upvotes)), 0.0)) desc