0

Please login or register to vote for this query.

(click on this box to dismiss)

List all the users with at least R reputation on any site in the data dump. Includes a link to the user's profile page on each site.

Q&A for professional and enthusiast 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 '~'
  "DisplayName COLLATE DATABASE_DEFAULT" avoids a collation conflict between Tex
  and the other sites. Thanks to Sem Vanmeenen (http://stackoverflow.com/questions/5026553/cannot-resolve-collation-conflict-for-column-4-in-select-statement
  and Tim Stone (http://data.stackexchange.com/stackoverflow/query/72411).
  The EmailHash could allow us to identify the same user on multiple sites
  (assuming that the user uses the same email address on every site), but this
  query does not use it yet.
 */
DECLARE @Template VARCHAR(MAX) = '(
  SELECT
    ~n AS [Site Name],
    ~u + ''/users/'' + Cast(Id AS VarChar(10)) + ''|'' + (DisplayName COLLATE DATABASE_DEFAULT) AS [User],
    Reputation,
    EmailHash
  FROM ~.Users
  WHERE Reputation >= ##Threshold##
)'
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 Reputation
'

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

fork query

edited jun 14 12

created jun 14 12 Gilles