=====**Queries for Politicians project**=====

===**Pyquals or Other Errors**===
Officials Missing Dates
%%(language-ref)
SELECT DISTINCT c.candidate_id, c.firstname, c.lastname, oc.office_id, o.name AS office
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN office o USING (office_id)
LEFT JOIN election_candidate ec
ON ec.candidate_id = c.candidate_id AND ec.office_id = oc.office_id
LEFT JOIN electionstage_candidate esc USING (election_candidate_id)
LEFT JOIN election_electionstage ees USING (election_electionstage_id)
WHERE (esc.electioncandidatestatus_id = 11 AND ees.electionstage_id = 'G') AND (lastelect is null OR firstelect is null OR nextelect is null)
ORDER BY c.lastname, c.firstname;
%%

Missing Left Office
%%(language-ref)
SELECT DISTINCT c.candidate_id, c.firstname, c.lastname, oc.office_id, o.name AS office
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN office o USING (office_id)
JOIN officetype ot USING (officetype_id)
WHERE oc.officecandidatestatus_id IN (0,2) AND oc.termend is null AND ot.officelevel_id IN ('F', 'S')
ORDER BY c.lastname, c.firstname
%%

Orphan Offices
%%(language-ref)
SELECT o.office_id, o.name
FROM office o
LEFT JOIN office_candidate oc USING (office_id)
LEFT JOIN election_candidate ec ON ec.office_id = o.office_id
WHERE oc.office_candidate_id IS NULL AND ec.election_candidate_id IS NULL;
%%

Multiple Candidates in Congstatus
%%(language-ref)
SELECT DISTINCT c.candidate_id, c.lastname, c.firstname, cs.congstatus_id, cong.congress_id, cong.billnumber, cong.state_id
FROM congress cong
JOIN congstatus cs USING (congress_id)
JOIN congstatus_candidate csc USING (congstatus_id)
JOIN office_candidate oc USING (office_candidate_id)
LEFT JOIN candidate c USING (candidate_id)
LEFT JOIN congstatus_candidate csc2
ON csc2.congstatus_id = csc.congstatus_id AND csc2.congstatus_candidate_id != csc.congstatus_candidate_id
LEFT JOIN office_candidate oc2
ON oc2.office_candidate_id = csc2.office_candidate_id AND oc2.candidate_id = oc.candidate_id
WHERE oc2.office_candidate_id IS NOT NULL;
%%

Missing Term Start After Election
%%(language-ref)
SELECT DISTINCT c.candidate_id, c.firstname, c.lastname, oc.office_id, o.name AS office
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN office o USING (office_id)
LEFT JOIN election_candidate ec
ON ec.candidate_id = c.candidate_id AND ec.office_id = oc.office_id
LEFT JOIN electionstage_candidate esc USING (election_candidate_id)
LEFT JOIN election_electionstage ees USING (election_electionstage_id)
WHERE (esc.electioncandidatestatus_id = 11 AND ees.electionstage_id = 'G' AND now() - interval '6 months' > ees.electiondate ) AND (termstart is null AND oc.officecandidatestatus_id = 1)
ORDER BY c.lastname, c.firstname;
%%

Orphan Candidate Check
%%(language-ref)
SELECT c.candidate_id, c.firstname, c.lastname
FROM candidate c
LEFT JOIN election_candidate ec USING (candidate_id)
LEFT JOIN office_candidate oc
ON oc.candidate_id = c.candidate_id
WHERE election_candidate_id IS NULL AND oc.office_candidate_id IS NULL;
%%

Bad Next Elect
%%(language-ref)
SELECT DISTINCT c.candidate_id, c.firstname, c.lastname, oc.office_id, o.name AS office
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN office o USING (office_id)
WHERE substring(oc.nextelect, '\d{4}')::int < extract(year from now()) AND oc.officecandidatestatus_id = 1
ORDER BY c.lastname, c.firstname
%%


===**Useful or Interesting Queries**===
Federal Active Candidates not currently holding office
%%(language-ref)
SELECT c.candidate_id, c.lastname, c.firstname, ec.state_id, o.name AS office, p.name AS party
FROM election e
JOIN election_candidate ec USING (election_id)
JOIN candidate c USING (candidate_id)
JOIN office o ON (ec.office_id = o.office_id)
JOIN electionstage_candidate esc USING (election_candidate_id)
JOIN electionstage_candidate_party escp USING (electionstage_candidate_id)
JOIN party p USING (party_id)
WHERE c.candidate_id NOT IN
(SELECT candidate_id FROM
(SELECT c.candidate_id, c.lastname, c.firstname, oc.state_id, o.name AS office, p.name AS party
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN office o ON (oc.office_id = o.office_id)
JOIN office_candidate_party ocp USING (office_candidate_id)
JOIN party p USING (party_id)
WHERE o.officetype_id = 'C' AND oc.officecandidatestatus_id = 1) x)
AND o.officetype_id = 'C' AND electionyear = 2014
GROUP BY c.candidate_id, c.lastname, c.firstname, ec.state_id, office, party
ORDER BY ec.state_id, o.name, c.lastname
%%

Query that lists all current (active) Congressional office holders who are not in U.S. Territories
%%(language-ref)
SELECT oc.office_candidate_id, c.candidate_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, oc.state_id, o.name office, oc.districtname_id, dn.name district
FROM candidate c
JOIN office_candidate oc USING (candidate_id)
JOIN office o USING (office_id)
LEFT JOIN districtname dn USING (districtname_id)
WHERE oc.officecandidatestatus_id = 1 AND o.officetype_id = 'C' AND oc.state_id NOT IN ('AS', 'DC', 'GU', 'MP', 'PR', 'VI')
ORDER BY oc.state_id, o.name DESC, oc.districtname_id ;
%%