Upvote/Downvote ratio per site

4

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250)
                       , votetypeid int
                       , numberofvotes 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 all'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , votetypeid
     , count(*)
from ' + quotename(name) + '.dbo.votes
where votetypeid IN (2, 3) group by votetypeid '
from sys.databases
where database_id > 5
  and (name not like '%.Meta' or name = 'StackExchange.Meta')

-- execute it
exec (@sql)

select u.site,
  u.numberofvotes AS [# upvotes],
  d.numberofvotes AS [# downvotes],
  ROUND(CAST(u.numberofvotes AS FLOAT) / d.numberofvotes, 2) AS [U/D ratio]
  from #results as u
  inner join #results as d
  on u.site = d.site
  where u.votetypeid = 2
    and d.votetypeid = 3
  order by CAST(u.numberofvotes AS FLOAT) / d.numberofvotes desc

drop table #results

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results