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)

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],
    Downvotes / 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

view current version

edited sep 10 12

created aug 30 12 Gilles