"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

-- This query will find the "home site" of all the voters in any given SE 
-- election, where "home site" is defined as the site where a voter has the most 
-- reputation. 
-- 
-- BEWARE: This query WILL NOT function correctly for elections conducted prior to
--         8 June 2012, which is when the Constituent badge was introduced.
-- BEWARE: This query looks at reputations of users _right now_, not as they were
--         when the election happened. If you want a snapshot of user reputations
--         as they were when they voted in the election, you should run this query
--         right after the election and save off a copy to look at. 
-- 
-- In order to use this query, you must do the following:
-- 1. Run it from the main site on the Data Explorer. So, if you want to know 
--    about a Math.SE election, you must run it from <http://data.stackexchange.com/math/>.
--    Not <http://data.stackexchange.com/meta.math/>.
-- 2. Set the ElectionStartDate and NextElectionStartDate parameters
--    appropriately. Set ElectionStartDate to a few days before the first day on which actual
--    votes were cast in the election; set NextElectionStartDate to the first
--    day on which the _following_ election started (use the beginning of
--    nominations for convenience and sureness). If you're interested in the most
--    recent election on a site, set NextElectionStartDate to some time far
--    in the future. Use YYYY-MM-DD format.
-- Everything else should work as-is. Just hit "Run Query" and you're good to go.
-- 
-- Created on account of <http://meta.math.stackexchange.com/q/18842/49701>


-- Grab list of voters
DECLARE @election_start_date datetime = ##ElectionStartDate:string##
DECLARE @next_election_start_date datetime = ##NextElectionStartDate:string##

-- <https://blog.stackexchange.com/2012/06/election-participation-badges/>
IF @election_start_date <= '2012-06-08'
BEGIN
  THROW 0, 'ElectionStartDate must be after 8 Jun 2012', 0
END

IF @election_start_date >= @next_election_start_date
BEGIN
  THROW 0, 'ElectionStartDate must be before NextElectionStartDate', 0
END

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', 'StackOverflow_temp')
    AND (name = 'StackExchange.Meta' OR name NOT LIKE '%.Meta') -- exclude per-site metas but keep Meta.SE
-- And declare some variables
DECLARE @sitedbname AS nvarchar(max)
DECLARE @query AS nvarchar(max)
CREATE TABLE #out (
  AccountId int,
  SiteName nvarchar(max),
  SiteReputation int
)

OPEN sites
FETCH NEXT FROM sites INTO @sitedbname
WHILE @@FETCH_STATUS = 0
BEGIN 
  SET @query = '
    USE [' + @sitedbname + ']
    
    INSERT INTO #out
    SELECT
      AccountId AS [AccountId],
      ''' + @sitedbname + ''' AS [SiteName],
      Reputation AS [SiteReputation]
    FROM Users
    WHERE
      AccountId IN (SELECT [AccountId] FROM #voters)
      AND Reputation > 101
    '
  EXEC sp_executesql @query
  
  FETCH NEXT FROM sites INTO @sitedbname
END
CLOSE sites
DEALLOCATE sites

SELECT
  COUNT(q.AccountId) AS [Count2],
  q.SiteName
FROM
  #out AS q
  INNER JOIN (
    SELECT DISTINCT AccountId, MAX(SiteReputation) AS [MaxReputation]
    FROM #out
    GROUP BY AccountId
  ) AS subq
    ON q.AccountId = subq.AccountId
WHERE q.SiteReputation = subq.MaxReputation
GROUP BY q.SiteName
ORDER BY [Count2] DESC

Enter Parameters

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