Average post score on all sites

1

Please login or register to vote for this query.

(click on this box to dismiss)

Show the average score for questions and for answers on all Stack Exchange sites.

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
    ~u + ''|'' + ~n AS [Site],
    Avg(Cast(Score AS Float)) AS [Posts],
    Avg(CASE PostTypeID WHEN 1 THEN Cast(Score AS Float) END) AS [Questions],
    Avg(CASE PostTypeID WHEN 2 THEN Cast(Score AS Float) END) AS [Answers]
  FROM ~.Posts
  WHERE (PostTypeId = 1 OR PostTypeId = 2)
)'
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 Posts DESC
'

/* And go! */
-- PRINT @Query
EXEC sp_executesql @Query

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results