13

Every Stack Exchange site moderator election has a box in the sidebar saying:

After 7 days, the top 30 nominees, ordered by reputation, advance to the primary phase. However, if there are 10 or fewer candidates, we skip directly to the election phase.

The bit about "top 30" has never been relevant in any election I've seen. Even in the last few elections on the flagship site Stack Overflow, there haven't been as many as 30 candidates. I was going to ask "has this ever been relevant outside of SO", but let me ask a slightly broader question:

which mod elections, outside of SO, have had the most candidates?

Obviously I could trawl through all sites' /election pages to find this information, but maybe someone knows an easier way (or has already collected the data).

2
  • Why is it important to leave SO out of the question in particular? Why not simplify and say "Which mod elections have had the most candidates on Stack Exchange?" . You seem to know that SO elections are not so populated. So why leaving SO out of the business? Has it a reason? Commented Aug 17, 2020 at 13:09
  • 2
    @RobertS SO pretty much always has bigger numbers than any other site, so asking "which mod elections have had the most candidates" would presumably just give me a list of the most crowded SO elections. I'm interested in the wider network, specifically whether the "top 30" thing has ever been relevant outside SO, but also how the biggest non-SO election compares with the biggest SO election (the latter would be easy to find). Commented Aug 17, 2020 at 13:11

2 Answers 2

13

Allow me to offer this SEDE query.

This will not return the correct result but it can help you find the correct answer when you apply the approach described in the answer by Glorfindel.

My query shows the ranking of elections based on all the actual nominations ever posted for that specific election. SE kindly still provides the nominations that were withdrawn as well.

I found no way in the public schema to determine that they were withdrawn. Which is why the answer from Glorfindel is correct and this one isn't.

So here is the multi-site beast with an insane temp table and temp index and an even more insane number of sub queries.

I rely in this query on the fact that nominations are recorded as posts with posttypeid = 6. These posts will only happen in a short 7 day period and these periods are significantly apart from each other.

By ordering the Posts on creationdate and using a LAG and LEAD function to find the time in days between posts you can find where the start and end of a period is, as those will have difference in days that is more then 7. The DateDiff function comes in handy there. I took 42 to be the tipping point, allowing for extended nomination periods. Once you have these start and end dates, their order determines what the election number is. From there it is a matter of joining the posts again on the date range found for each election to give the number of nominees for an election.

The column site name links to the correct election page. There you can find the number of actual candidates. That will also tell how many nominees withdrew, or were withdrawn, if you compare that with the number in the SEDE query result.

declare @sql nvarchar(max)

create table #result (site sysname
                    , hostname sysname
                    , id integer
                    , creationdate datetime
                    )
                    
create index #idx_hn_cd on #result(hostname, creationdate)


select @sql = N'insert into #result' + STRING_AGG(concat(N'
select ''', name ,N''' as site
     , ''',hostname ,N'''
     , pm.id  
     , pm.creationdate
    from ', quotename(name), N'.dbo.posts pm
    where pm.posttypeid = 6
'),N' 
union all')
from (select convert(nvarchar(max),name) name
    -- , convert(nvarchar(max),sitename)  sitename
    -- , meta
    -- , domain
     ,  concat( 
        -- based on an idea from Glorfindel 
        -- https://meta.stackexchange.com/a/321181
        (case sitename
        WHEN 'Audio' THEN 'video'
        WHEN 'Beer' THEN 'alcohol'
        WHEN 'CogSci' THEN 'psychology'
        WHEN 'Garage' THEN 'mechanics'
        WHEN 'Health' THEN 'medicalsciences'
        WHEN 'Moderators' THEN 'communitybuilding'
        WHEN 'Photography' THEN 'photo'
        WHEN 'Programmers' THEN 'softwareengineering'
        WHEN 'Vegetarian' THEN 'vegetarianism'
        WHEN 'Writers' THEN 'writing'
        WHEN 'Br' THEN 'pt'
        WHEN 'Mathoverflow' THEN concat((meta+'.'), sitename)
        ELSE case when sitename = domain then null else sitename end
        end +'.')
        , (case 
           when sitename <> 'mathoverflow' then (meta+'.') 
           else null 
           end) 
        , (case 
           when sitename <> 'mathoverflow' then concat((domain + '.'), 'com') 
           else 'net' 
           end)
        ) hostname
from (
select name
, case parsename(name,1) 
  when 'Meta' then parsename(name,2)
  else parsename(name,1) 
  end [sitename]
, case parsename(name,1) 
  when 'Meta' then 'meta'
  else null
  end [meta]
  , coalesce(parsename(name,3), parsename(name,2)) [domain]
from sys.databases
where database_id > 5
and (name not like '%.Meta') -- only main sites have elections
) dbs
) dbsall

exec (@sql)

select rank() over (order by count(*) desc) [ranking]
     , s2 [Site name]
     , election [election]
     , start
     , [end]
     , count(*) [# nominees]
from 
(
select ele_data.s2
     , election
     , min(ele_data.creationdate) start
     , max(ele_data.creationdate) [end]
from
(
  select data.s1 s2
  , ceiling((row_number() over(partition by data.s1 order by creationdate) ) / 2.0) election
     --  , min(creationdate) se
     --  , max(creationdate) ee
        , *
  from (
    select #result.site [s1]
         , id 
         , datediff(
           d
           , lag(creationdate, 1 , '2008-09-01') over(partition by site order by creationdate)
           , creationdate) [days prev]
         , datediff(
           d
           , creationdate
           ,lead(creationdate, 1 , getdate()) over(partition by site order by creationdate)
            ) [days next]
         , creationdate
    from #result
  ) data
  where [days prev] > 42 or [days next]> 42
) ele_data
group by ele_data.s2, election
) rollup
inner join #result r 
   on r.site = s2 
   and r.creationdate between  start and [end]
where s2 <> 'StackOverflow'
group by s2, election, start, [end]
order by count(*) desc

When run today this is what the top result looks like:

Top sites: math, election 5, 34 nominees; Magento, election 2, 24 nominees; Ubuntu, election 5, 22 nominees

Keep in mind SEDE is updated once a week on Sunday.
Let's praise the kind Monica Cellio for the awesome SEDE Tutorial she wrote.
Say "Hi" in SEDE chat.

12

Three elections with lots of candidates (just from memory, I haven't scraped them – perhaps ArtOfCode has some code for this):

This does not count candidates whose nominations were deleted (because of being recently suspended, or posting an obvious joke-nomination).

10
  • It was still 20 for the primary in the most recent math election, if the info on the side was correct. Maybe it differs by site. Commented Aug 16, 2020 at 18:51
  • "In the nomination phase, any community member in good standing with more than 1,233 reputation may nominate themselves to be a community moderator." That's a weirdly specific number (from the Maths election you link to). Commented Aug 16, 2020 at 19:23
  • I knew about the 1,000 reputation requirement, but 1,233 sounds odd (and not because it's an odd number). And the 20 candidates limit may well be a Math-specific thing too.
    – Glorfindel Mod
    Commented Aug 16, 2020 at 19:34
  • 5
    The 1233 requirement is because that was, at the time, the lowest reputation of the candidate in the top 20 (the then threshold for primary) when sorted by reputation. Thus, if one would be outside the primary threshold when submitting candidacy, one is unable. Commented Aug 16, 2020 at 20:56
  • something is weird because in SEDE I find 32 nomination posts: data.stackexchange.com/math/query/1279661
    – rene
    Commented Aug 16, 2020 at 22:11
  • 1
    @rene perhaps some of them were deleted, even though SEDE usually doesn't contain much information about deleted posts – this could be an edge case; I do see DeletionDate is empty.
    – Glorfindel Mod
    Commented Aug 17, 2020 at 11:23
  • @Glorfindel yeah, maybe I'll poke an CM to tell us that went wrong there
    – rene
    Commented Aug 17, 2020 at 11:24
  • @rene Could it be that some valid nominees (i.e. nominations not deleted) later deleted their accounts? IIRC, if someone deletes their account it usually messes up their nomination posts in past elections. Commented Aug 17, 2020 at 13:15
  • 1
    @Randal'Thor there are two entries with no OwnerUserId, I presume those are from now-deleted accounts.
    – Glorfindel Mod
    Commented Aug 17, 2020 at 13:21
  • 1
    @Randal'Thor your guess is as good as mine and that case is a possibility but it doesn't cover all cases I see in the example I provided nor for the other elections I poked in. I asked a CM to look into this and they kindly agreed to pass my message on.
    – rene
    Commented Aug 17, 2020 at 13:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .