8

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)

Q&A for people organizing, marketing or licensing open source development projects

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

fork query

edited may 20 14 rolfl

created aug 30 12 Gilles