Average number of votes on all sites

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)

Economics

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
    *
  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 AS v
    WHERE VoteTypeId = 2 OR VoteTypeId = 3
  ) JOIN (
    SELECT
      ~u + ''|'' + ~n AS [Site],
      Count(*) AS [Posts]
    FROM ~.Posts AS p
    WHERE PostTypeId = 1 OR PostTypeId = 2
  ) 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
  ), '&gt;', '>'), '&lt;', '<'), '&amp;', '&') -- 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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results