1

Which question or answer in all of Stack Exchange had the most upvotes and downvotes combined? Under the criteria the following criteria: Upvote percentage must be between 40% and 60% (inclusive).

Rarely do you see questions or answers with an upvote percentage between that range. It's either al(most) all upvoted or all downvoted.

  • Including or excluding meta sites? Because if it including, uh. This actually is pretty common. – Aza Oct 26 '17 at 16:57
  • Would love to see it on both meta and regular sites. – puretppc Oct 26 '17 at 17:02
3

This query does exactly that. If you run it, you will see a list of posts that have more than half the amount of downvotes as they have upvotes (which means that they have between 33% and 67% upvotes). The posts are ordered by the amount of upvotes.

For example, from this query, I was able to find that this answer is the most controversial post on Stack Overflow (it has 406 upvotes and 242 downvotes for those that don't have 1k reputation on Stack Overflow) and this question is the most controversial post on Meta Stack Exchange (it has 234 upvotes ans 122 downvotes for those who don't have 1k reputation here). If you're looking for posts that have a number of upvotes and a number of downvotes that are very close to each other, using the query, I found this answer on Stack Overflow (139 upvotes and 120 downvotes) and this question on Meta Stack Exchange (82 upvotes and 60 downvotes).

2

This is the network wide query that does what you ask for.

When run today these are top post with Introducing the Developer Story being the "winner" (based on ordering on total votes):

enter image description here

Here is the actual SQL query:

-- start create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO
-- create url based on dbname
create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @p1 nvarchar(250) = parsename(@dbname,1)
      declare @p2 nvarchar(250) = parsename(@dbname,2)
      declare @p3 nvarchar(250) = parsename(@dbname,3)
      set @res = 
        (case  @dbname 
         when 'StackExchange.Meta' then 'Meta.StackExchange'
         when 'StackOverflow.Meta' then 'Meta.StackOverflow'
         when 'StackExchange.Audio' then 'avp.StackExchange'
         when 'StackExchange.Audio.Meta' then 'Avp.Meta.StackExchange'
         when 'StackExchange.Mathoverflow.Meta' then 'meta.Mathoverflow.net'
         when 'StackExchange.Mathoverflow' then 'Mathoverflow.net'
      else (case when @p1 = 'Meta' then @p2 else @p1 end) + 
           (case when @p2 is null then '' else '.' end) +
           (case when @p1 = 'Meta' then @p1 else isnull(@p2,'') end) +
           (case when @p3 is null then '' else '.' end) +
           isnull(@p3,'')     
      end) + '.com'
   end;
GO

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE
                        -- and [name] not like 'StackOverflow'

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_posts ( site nvarchar(250)
                            , title nvarchar(250)
                            , postid int
                            , upvotes int
                            , downvotes int);

open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    set @sql = N'
    use '+ QUOTENAME(@db_c_name) +';
    declare @url nvarchar(250)
    exec #siteurl ''' + @db_c_name  + ''', @url output
    insert into #all_posts 
    select -- top 10
      @url
     , ''tbd''
     , v.postid
     , sum(case when v.votetypeid = 2 then 1 else 0 end) 
     , sum(case when v.votetypeid = 3 then 1 else 0 end) 
     from-- posts p
     -- inner join posts q on q.id = coalesce(p.parentid, p.id) and q.posttypeid = 1
     -- inner join 
     votes v -- on v.postid = p.id
     where v.votetypeid in (2,3)
     -- and p.posttypeid in (1,2) 
     group by -- q.title,
             v.postid
     having (cast(sum(case when v.votetypeid = 2 then 1 else 0 end) as decimal) /
            (sum(case when v.votetypeid = 3 then 1 else 0 end) 
            + sum(case when v.votetypeid = 2 then 1 else 0 end)))
     between 0.4 and 0.6
     order by sum(case when v.votetypeid = 3 then 1 else 0 end) 
            + sum(case when v.votetypeid = 2 then 1 else 0 end) desc
     '
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select replace(
       replace(
       replace(site,
         '.StackExchange.com',''),
         '.com',''),
         '.net','') as [site]
     , 'http://' 
     + site 
     + '/q/'
     + cast(postid as nvarchar)
     + '| Post Id: '
     + cast(postid as nvarchar) as post
     , upvotes
     , downvotes
from #all_posts
order by  upvotes + downvotes desc

drop table #all_posts

drop procedure #siteurl

Due to the resource hungry nature of the join needed to get the post title I could only make this work by just querying the votes table for each site. Due to that I can only offer the ID of the post for a specific site. Just click the link to find the actual post.

The query includes all main sites and site meta's.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .