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'''
from ', quotename(name), N'.dbo.posts pm
where pm.posttypeid = 6
from (select convert(nvarchar(max),name) name
-- , convert(nvarchar(max),sitename) sitename
-- , meta
-- , domain
-- based on an idea from Glorfindel
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
when sitename <> 'mathoverflow' then (meta+'.')
when sitename <> 'mathoverflow' then concat((domain + '.'), 'com')
, case parsename(name,1)
when 'Meta' then parsename(name,2)
, case parsename(name,1)
when 'Meta' then 'meta'
, coalesce(parsename(name,3), parsename(name,2)) [domain]
where database_id > 5
and (name not like '%.Meta') -- only main sites have elections
select rank() over (order by count(*) desc) [ranking]
, s2 [Site name]
, election [election]
, count(*) [# nominees]
, min(ele_data.creationdate) start
, max(ele_data.creationdate) [end]
select data.s1 s2
, ceiling((row_number() over(partition by data.s1 order by creationdate) ) / 2.0) election
-- , min(creationdate) se
-- , max(creationdate) ee
select #result.site [s1]
, lag(creationdate, 1 , '2008-09-01') over(partition by site order by creationdate)
, creationdate) [days prev]
,lead(creationdate, 1 , getdate()) over(partition by site order by creationdate)
) [days next]
where [days prev] > 42 or [days next]> 42
group by ele_data.s2, election
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:
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.