"Home site" of election voters

1

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics

Q&A for people studying math at any level and professionals in related fields

-- Grab list of voters
DECLARE @election_start_date datetime = '2014-12-14'
DECLARE @next_election_start_date datetime = '2100-01-01'

SELECT 
  u.AccountId AS [AccountId]
INTO
  #voters
FROM
  Users AS u
  INNER JOIN Badges AS b
    ON u.Id = b.UserId
WHERE
  b.Name = 'Constituent'
  AND DATEDIFF(MINUTE, @election_start_date, b.Date) > 0
  AND DATEDIFF(MINUTE, DATEADD(DAY, -1, @next_election_start_date), b.Date) < 0


-- 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 (name = 'StackExchange.Meta' OR name NOT LIKE '%.Meta') -- exclude per-site metas
-- And declare some variables
DECLARE @sitedbname AS nvarchar(max)
DECLARE @sitehostname AS nvarchar(max)
-- These variables are "optional" but useful
DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
  SiteName nvarchar(max),
  AccountId int,
  SiteReputation 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 sites 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'
  ----------------------------------------
  -- 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 --------------------------------------
  -----------------------------------------------------------------------------
  
  SET @query = '
    USE [' + @sitedbname + ']
    
    INSERT INTO #out
    SELECT
      ''' + @sitedbname + ''' AS [SiteName],
      AccountId AS [AccountId],
      Reputation AS [SiteReputation]
    FROM
      Users
    WHERE
      AccountId IN (SELECT [AccountId] FROM #voters)
      AND Reputation > 101
    '
  --PRINT @query
  EXEC sp_executesql @query
  
  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites

-- Reap results
SELECT
  q.SiteName, 
  q.AccountId
FROM
  #out AS q
  INNER JOIN (SELECT AccountId, MAX(SiteReputation) AS [MaxReputation]
              FROM #out
              GROUP BY AccountId) AS subq
    ON q.AccountId = subq.AccountId
WHERE
  q.SiteReputation = subq.MaxReputation

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results