For each site, show the average number of votes per post (questions and answers alike)
Q&A for those who study, teach, research and apply economics and econometrics
/* 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 '~' */ DECLARE @Template VARCHAR(MAX) = '( SELECT v.Site, Votes / Posts AS [Votes], Upvotes / Posts AS [Upvotes], Downvotes / Posts AS [Downvotes] FROM ( SELECT ~u + ''|'' + ~n AS [Site], Count(*) AS [Votes], Count(CASE VoteTypeID WHEN 2 THEN 1 END) AS [Upvotes], Count(CASE VoteTypeID WHEN 3 THEN 1 END) AS [Downvotes] FROM ~.Votes WHERE VoteTypeId = 2 OR VoteTypeId = 3 ) AS v JOIN ( SELECT ~u + ''|'' + ~n AS [Site], Count(*) AS [Posts] FROM ~.Posts WHERE PostTypeId = 1 OR PostTypeId = 2 ) AS p ON v.Site = p.Site )' DECLARE @Separator VARCHAR(MAX) = ' UNION ALL ' /*Perform the escape string replacement*/ DECLARE @Query NVARCHAR(MAX) = Replace(Replace(Replace(( SELECT Replace(Replace(Replace(Replace(@Template, '~n', '''' + Replace(Left([Name], Len([Name])-9), 'StackExchange.', '') + ''''), '~u', '''http://' + Lower( CASE WHEN [Name] LIKE 'StackExchange.%.Meta.Exported' THEN 'meta.' + Substring([Name], 15, Len([Name])-28) + '.stackexchange' WHEN [Name] LIKE 'StackExchange.%.Exported' THEN Substring([Name], 15, Len([Name])-23) + '.stackexchange' WHEN [Name] LIKE '%.Meta.Exported' THEN 'meta.' + Left([Name], Len([Name])-14) ELSE Left([Name], Len([Name])-9) END) + '.com'''), '~.', '[' + Name + ']..'), '~~', '~') + @Separator FROM sys.Databases WHERE Name LIKE '%.Exported' -- omit metas, except MetaSO -- AND (NOT Name LIKE '%.Meta.Exported' OR Name = 'StackOverflow.Meta') FOR XML PATH('') -- Concatenate the rows, thanks to http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 ), '>', '>'), '<', '<'), '&', '&') -- we used FOR XML, so we need to de-XMLify /* Add the prelude and postlude */ SELECT @Query = ' SELECT * FROM ( ' + Substring(@Query, 1, Len(@Query) - Len(@Separator)) + ' ) AS FromAllSites ORDER BY Votes DESC ' /* And go! */ -- PRINT @Query EXEC sp_executesql @Query