Average number of votes on all sites

7

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)

Linguistics Meta

Q&A about the site for professional linguists and others with an interest in linguistic research and theory

/*
  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
BEGIN

    print 'Processing site ' + @sitedbname
    set @query = '
           insert into #sitevotes
           select ''' + @sitedbname + ''',
                  pc.PostCount,
                  v.UpVotes,
                  v.DownVotes
                  FROM (select Count (*) as PostCount
                        from [' + @sitedbname + ']..Posts
                        where PostTypeId in (1, 2)) as pc,
                       (Select 
                            sum(CASE VoteTypeID WHEN 2 THEN 1 else 0 END) as UpVotes,
                            sum(CASE VoteTypeID WHEN 3 THEN 1 else 0 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
END

close SITES
deallocate SITES

select
    Site,
    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

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