Average number of votes on all sites

1

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)

Stack Overflow

Q&A for programmers

/*
  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],
    Cast (Downvotes AS Float) / Upvotes AS [Down/Up ratio]
  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],
      Cast(Count(*) AS Float) 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
  ), '&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 sites:
loading Hold tight while we fetch your results