Get office-holders per election

[jmcarp]

It would be helpful for a few different parts of the application to know which candidate won a given election (e.g. election lookup, election detail, but I think this has come up in other places too). We could try to infer this from incumbent / challenger status, but then we'd miss elections in which the incumbent didn't run. Based on discussion earlier, it sounds like @PaulClark2 might have more information. This is me reminding Paul to see if FEC might have this data already, time permitting.

[PaulClark2]

@jmcarp

You should see a candidate list table (cand_valid_fec_yr) tomorrow or Thursday.
CAND_VALID_FEC_YR (a candidate list by 2-year period including candidate election year and incumbent-challenger status)

In addtion to the candidate list I've asked Rohan to push the tables below to you:
CMTE_VALID_FEC_YR (a committee list by 2-year period)
CANDIDATE_SUMMARY (candidate financial summaries from 2007-2008 to present)
COMMITTEE_SUMMARY (committee financial summaries from 2007-2008 to present)
CAND_CMTE_LINKAGE (provides candidate to committee linking information)

[jmcarp]

Thanks @PaulClark2! But I'm still not sure this is enough information to figure out who won a given election--specifically in the case where the incumbent doesn't run in the next election, or the next election hasn't happened yet. For example, Jim Webb won the Virginia Senate election in 2006 but didn't run in 2012, so he's not going to show up as an incumbent, so I can't infer that he was in office to begin with. Same question about elections for which the next race hasn't happened yet--is there a way to figure out that Mark Warner won the Virginia Senate race in 2014?

[PaulClark2]

@jmcarp doesn't this get you the information you want?

select
cand_id, cand_name, cand_ici, fec_election_yr, cand_election_yr
from CAND_VALID_FEC_YR where cand_id = 'S6VA00127';

[PaulClark2]

I'm not sure Warner is a problem.

[PaulClark2]

In Arkansas the incumbent lost in 2014 but shows up as an incumbent for 2014 as he should.

[jmcarp]

Got it, the piece I didn't understand was that candidates are recorded as incumbents in future elections. So it looks like I can get a list of the candidates who won House elections in North Carolina in 2014 like this:

select cand_name from cand_valid_fec_yr where cand_office = 'H' and cand_office_st = 'NC' and cand_ici = 'I' and cand_election_yr = 2016;

And candidates who won Senate races in 2014 like this:

select cand_name from cand_valid_fec_yr where cand_office = 'S' and cand_ici = 'I' and cand_election_yr = 2020;

It seems like the method to build these queries is to filter where cand_election_yr is two years after the election for House races, six years after for Senate races, etc. But I still haven't found a query that tells me that Obama won reelection in 2012. For example, this query has no results:

select cand_name from cand_valid_fec_yr where cand_office = 'P' and cand_ici = 'I' and cand_election_yr = 2016;

@PaulClark2: Is there a query I can use to figure out the outcomes of any Presidential race? Thanks for bearing with me on this.

[PaulClark2]

@jmcarp Josh your House and Senate solutions seem reasonable to me and when I run those queries I get results I expect. For the presidential query try using fec_election_yr instead of cand_election_yr or if you can construct your query so that if gets the cand_election_yr right for presidential candidates cand_election_yr would work. As you know, presidential candidates should only have elections years equal to 1976, 1980, 1984, 1988, 1992, 1996, 2000, 2004, 2008, 2012, 2016, etc.

select * from CAND_VALID_FEC_YR where fec_election_yr= 2016 and cand_office = 'P' and cand_ici = 'I';

[jmcarp]

Merged and deployed to dev.