Average number of votes on all sites


Please login or register to vote for this query.

(click on this box to dismiss)

For each site, show the average number of votes per post (questions and answers alike)

Music Fans Meta

Q&A about the site for music historians, critics, and fans

  Inspiration for cross-site query from:
    Mark Hurd, http://data.stackexchange.com/stackoverflow/query/69700/generate-emailhash-comparison
    Tim Stone, http://data.stackexchange.com/stackoverflow/query/72411
  We build a dynamic query as a prelude, then @Template for each site
  with @Separator in between, and finally a postlude.
  First, the template, which may contain the following escape strings:
    ~n  the "pretty" site name (or close enough), e.g. 'Dba'
    ~u  the URL of the site, e.g. http://dba.stackexchange.com
    ~~  a literal '~'
create table #sitevotes (
    Site NVarchar(100),
    Posts float,
    UpVotes float,
    DownVotes float);
declare @sitedbname as NVarchar(100),
        @query as NVarchar(1000)

declare SITES cursor for

select Substring(Name, 0, Len(Name) - 4 )
from sys.Databases
where Name like '%.Meta'
  --and Name = 'StackExchange.Codereview.Meta'
  and not Name = 'StackExchange.Meta'
UNION select 'StackExchange.Meta'

open SITES

fetch next from SITES into @sitedbname
while @@FETCH_STATUS = 0

    print 'Processing site ' + @sitedbname
    set @query = '
           insert into #sitevotes
           select ''' + @sitedbname + ''',
                  FROM (select Count (*) as PostCount
                        from [' + @sitedbname + ']..Posts
                        where PostTypeId in (1, 2)) as pc,
                            Count(CASE VoteTypeID WHEN 2 THEN 1 END) as UpVotes,
                            Count(CASE VoteTypeID WHEN 3 THEN 1 END) as DownVotes
                        from [' + @sitedbname + ']..Votes
                        where VoteTypeId in (2, 3)) as v'
 --          ''' + @sitedbname + '''.Votes
 --          WHERE VoteTypeId in (2, 3)
 --          group by ';
    print @query

    EXEC sp_executesql @query

    fetch next from SITES into @sitedbname

close SITES
deallocate SITES

    Posts as [PostCount],
    (UpVotes + DownVotes) as [VoteCount],
    cast((UpVotes + DownVotes) / Posts as Decimal(10,4)) as [VoteRatio],
    cast(UpVotes / Posts as Decimal(10,4)) as [UpVoteRatio],
    cast(DownVotes / Posts as Decimal(10,4)) as [DownVoteRatio],
    cast(UpVotes / DownVotes as Decimal(10,4)) as [Up/Down Ratio]
from #sitevotes
order by [VoteRatio] Desc

Enter Parameters

Switch to main site
loading Hold tight while we fetch your results