Sites by maximal number of profile-trackable badges reached

1

Please login or register to vote for this query.

(click on this box to dismiss)

Individual-site breakdowns at http://data.stackexchange.com/stackoverflow/query/780210/users-by-number-of-profile-trackable-badges-obtained

Stack Overflow

Q&A for professional and enthusiast programmers

-- Create cursor for list of sites
DECLARE sites CURSOR FOR
  SELECT name
  FROM sys.databases
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb', 'Data.StackExchange')
-- And declare some variables
DECLARE @sitedbname AS nvarchar(max)
DECLARE @sitehostname AS nvarchar(max)
DECLARE @ispersitemeta AS bit

DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
  Site nvarchar(max) NOT NULL,
  -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  -- COLUMN NAMES WANT TO ADD SHOULD GO HERE   
  NumberOfBadges int 
  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
)
-- These variables are for the SPOOKY HOSTNAME GENERATION CODE
DECLARE @spooky_string AS nvarchar(max)
DECLARE @spooky_delimiter AS char(1) = '.'
DECLARE @spooky_xml AS xml
DECLARE @spooky_result AS nvarchar(max)

-- Step through cursor
OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN
  -----------------------------------------------------------------------------
  -- BEGIN SPOOKY HOSTNAME GENERATION CODE ------------------------------------
  -- adapted from <http://data.stackexchange.com/stackoverflow/query/256747/> -
  -----------------------------------------------------------------------------
  SET @spooky_string = @sitedbname
  SET @spooky_xml = CAST(('<X>' + REPLACE(@spooky_string, @spooky_delimiter, '</X><X>') + '</X>') AS xml)
  SET @spooky_result = ''

  SELECT
    C.value('.', 'nvarchar(max)') AS [Piece],
    C.value('for $i in . return count(../*[. << $i]) + 1', 'int') AS [Index]
  INTO #spooky_pieces
  FROM @spooky_xml.nodes('X') AS X(C)

  SELECT @spooky_result = COALESCE(@spooky_result + '.', '') + [Piece]
  FROM #spooky_pieces
  ORDER BY [Index] DESC

  DROP TABLE #spooky_pieces

  SET @sitehostname = 'http://' + RIGHT(@spooky_result, LEN(@spooky_result)-1) + '.com'
  SET @ispersitemeta = (CASE WHEN @sitedbname LIKE '%Meta%' AND @sitedbname != 'StackExchange.Meta' THEN 1 ELSE 0 END)

  ----------------------------------------
  -- HERE COME THE SPOOKY SPECIAL CASES --
  ----------------------------------------
  -- Meta MathOverflow doesn't have a redirect; see <http://meta.stackexchange.com/q/215071/224428>
  IF @sitedbname = 'StackExchange.Mathoverflow.Meta' SET @sitehostname = 'http://Meta.MathOverflow.net'
  -- For some reason probably involving the AVP/Audio/Video/Sound hullabaloo, there is 
  -- still a StackExchange.Audio DB that's getting updated. http://audio.stackexchange.com/
  -- no longer exists, so we use Video.SE for the hostname instead.
  IF @sitedbname = 'StackExchange.Audio' SET @sitehostname = 'http://Video.StackExchange.com'
  -----------------------------------------------------------------------------
  -- END SPOOKY HOSTNAME GENERATION CODE --------------------------------------
  -----------------------------------------------------------------------------

  -- ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
  -- CODE YOU WANT TO ADD SHOULD GO HERE
  -- for example,
  SET @query = '
    USE [' + @sitedbname + ']

    INSERT INTO #out
    SELECT
      ''' + @sitehostname + '|' + @sitedbname + ''' AS [Site],
      (SELECT MAX(NumberOfBadges) FROM(
      SELECT 
COUNT(DISTINCT Name) AS NumberOfBadges
FROM Badges
WHERE Name IN (''Copy Editor'', ''Electorate'', ''Fanatic'', ''Illuminator'',
               ''Legendary'', ''Marshal'', ''Socratic'')
   OR Name IN (''Archaeologist'', ''Civic Duty'', ''Deputy'', ''Enthusiast'', 
               ''Epic'', ''Inquisitive'', ''Necromancer'', ''Refiner'', 
               ''Research Assistant'', ''Sportsmanship'', ''Strunk & White'')
   OR Name IN (''Altruist'', ''Autobiographer'', ''Benefactor'', ''Citizen Patrol'', 
               ''Cleanup'', ''Commentator'', ''Critic'', ''Curious'', ''Custodian'', 
               ''Disciplined'', ''Editor'', ''Excavator'', ''Explainer'', ''Informed'', 
               ''Investor'', ''Mortarboard'', ''Organizer'', ''Peer Pressure'', 
               ''Promoter'', ''Proofreader'', ''Revival'', ''Scholar'', 
               ''Self-Learner'', ''Student'', ''Suffrage'', ''Supporter'', 
               ''Tag Editor'', ''Teacher'', ''Vox Populi'')
GROUP BY UserId
) BadgeNumberTable) AS NumberOfBadges
    '
  EXEC sp_executesql @query
  -- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑

  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites

-- Reap results (also optional)
SELECT
  *
FROM
  #out
ORDER BY 'NumberOfBadges' DESC

Enter Parameters

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