How to find the list of most upvoted questions/answers on a SE site that is not Stack Overflow?

0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/questions/322600/how-to-find-the-list-of-most-upvoted-questions-answers-on-a-se-site-that-is-not/322609#322609

Stack Overflow

Q&A for professional and enthusiast programmers

declare @sql nvarchar(max)

create table #result( site sysname
                    , postid integer
                    , posttypeid integer
                    , title nvarchar(250)
                    , votetypeid integer
                    , votes integer);

select @sql = concat('insert into #result', string_agg(concat(N'
select * from (select top 1
 ''' , convert(nvarchar(max),name), N''' site 
     , v.postid 
     , p.posttypeid
     , q.title
     , v.votetypeid
     , count(*) [#votes]
from ',convert(nvarchar(max),quotename(name)), N'.dbo.votes v
inner join ', convert(nvarchar(max),quotename(name)), N'.dbo.posts p on p.id = v.postid
inner join ', convert(nvarchar(max),quotename(name)), N'.dbo.posts q on q.id = nullif(p.parentid, p.id)
where p.posttypeid in (1,2) -- q, a
and v.votetypeid = 2 -- up
group by v.postid, q.title, p.posttypeid, v.votetypeid
order by count(*) desc
) data
'), 'union all'))
from sys.databases
where database_id > 5
and (name not like '%.meta' or name = 'stackexchange.meta')
and not (name = 'stackoverflow')

print @sql

exec (@sql)

select 
  site
  , concat('https://'
   , case 
     when len(parsename(site,2)) > 0 then
       concat(parsename(site,1),'.',parsename(site,2))
     else
       site
     end
    , '.com'
    , '/q/'
    , postid
    , '|'
    , case posttypeid when 1 then '[Q] ' else '[A] on ' end
    , title
    ) [Post Link]
  , case votetypeid when 2 then 'Up' else 'Down' end [Up/Down]
  , votes
from #result
order by votes desc

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results