For each site, show the average number of votes per post (questions and answers alike)
Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Japanese language
/* 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