6

In the Stack Exchange Data Explorer you can find the Moderator nominations by querying the Posts Table for posttypeid = 6, like so:

select id as [Post Link]
     , score
     , owneruserid as [User Link]
     , creationdate
from posts
where posttypeid = 6
order by score desc

As you can see I used the Post Link magic column, however that link doesn't work, in the first place because there is no title in those particular rows nor does the link bring you to the nomination post. Funny enough revisions does work.

Looking at a specific election page, for example election 8 on SO I can find the nomination posts and looking at the html, a nomination is wrapped in div with an post id:

<div style="border-bottom: 1px solid #AAA; padding-bottom: 20px;" id="post-40474659">
</div>

So for Aaron Hall I would need an url that looks like this:

https://stackoverflow.com/election/8#post-40474659

where 40474659 is the postid of Aaron's nomination. What I'm missing is the 8 being the number of the election.

I did look at Magic number in Data Explorer should be documented and of course the standard Database schema documentation for the public data dump and SEDE but none of those mention the election part of nominations.

For a given moderator nomination how can I obtain the election number?

4

The election number isn't available in the schema. You have to obtain it by grouping the creationdates of the nomination posts of the candidates. I've done the grouping here by joining the rows that are within +/- 16 days from each other (to allow for elections that got extended). Once you have that you can find the max(creationdate) per group which allows you to do a dense_rank over it.

My method will fail if an election gets cancelled without candidates. It also fails for some candidates in the January 2011 election. Somehow SEDE tells me there are 81 nomination posts while the election page only shows 55. I already found a comment from Jasonc with a possible fix but that doesn't seem to work anymore. I don't know if SEDE is wrong or the election page.

Anyway, here is the query:

select election
     , 'site://election/' 
     + cast(election as nvarchar)
     + '?tab=nomination&all=true#post-'
     + cast(ep.id as nvarchar)
     + '|'
     + coalesce(u.displayname, ownerdisplayname) as [nomination post]
from
(
  select dense_rank() over(order by max_nom) election
       , id
  from
  (
    select pm.id  
         , min(pm.creationdate) min_nom
         , max(pg.creationdate) max_nom
    from posts pm
    inner join posts pg on pg.creationdate 
          between dateadd(d, -16, pm.creationdate)   -- roughly 14 days
             and dateadd(d, +16, pm.creationdate) 
    where pm.posttypeid = 6
    and   pg.posttypeid = 6
    group by pm.id
  ) data
) ep
inner join posts p on p.id = ep.id
left outer join users u on p.owneruserid =u.id
order by election

It will give you a clickable list that brings you in most cases to the nomination post in the correct election.

You must log in to answer this question.

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