Profiles Error Queries
This is a list of queries that should be run every few months to make sure our data is free of errors that are inconsistent with the whole of the Profile Department
current, not present - This query finds all the times 'current' is in organization. We don't like to use the word current in bios. In the WHERE clause, change e.organization to e.span to find all uses of current in Span:
SELECT e.candidate_id, e.organization, e.span FROM experience e JOIN candidate c USING (candidate_id) JOIN office_candidate oc USING (candidate_id) JOIN office o USING (office_id) WHERE o.officetype_id = 'L' AND e.organization LIKE '%current%' OR o.officetype_id = 'L' AND e.span LIKE '%current%' ORDER BY candidate_id
Education - This query shows all degrees listed with periods in them, which is a big no-no:
SELECT candidate_id, degree FROM education WHERE degree LIKE '%B.A%' OR degree LIKE '%M.A%' OR degree LIKE '%B.S.%' OR degree LIKE '%M.S%'
Office Experience - This query Finds any candidate in the 2016 election that no longer holds a Congressional or State Legislative office but have "present" in their office experience entry. Written for the 2016 election, alter el.election year for future elections or remove completely to find all instances in our database:
SELECT oc.candidate_id, e.title, e.organization, e.span, oc.officecandidatestatus_id FROM office_candidate oc JOIN experience e ON (e.candidate_id = oc.candidate_id) JOIN election_candidate ec ON (ec.candidate_id = oc.candidate_id) JOIN election el ON (el.election_id = ec.election_id) JOIN officecandidatestatus ocs ON (ocs.officecandidatestatus_id = oc.officecandidatestatus_id) WHERE experience_id NOT IN (SELECT e.experience_id FROM experience e JOIN office_candidate oc ON (oc.candidate_id = e.candidate_id) WHERE oc.officecandidatestatus_id = 1) AND (e.title iLIKE '%representative%' OR e.title iLIKE '%Senator%' OR e.title iLIKE '%Assembly%' OR e.title iLIKE '%Minority%' or e.title iLIKE '%speaker%' or e.title iLIKE '%majority%') AND e.span iLIKE '%present%' AND el.electionyear = 2016 AND e.experiencetype_id iLIKE 'P' ORDER by candidate_id, e.organization, e.title, e.span
Missing Cats and Tags - Lists the candidates with at least 5 entries lacking BOTH cats and tags. Written for the 2016 election, alter el.election year for future elections:
SELECT count (e.candidate_id), e.candidate_id FROM experience e JOIN election_candidate ecc ON (ecc.candidate_id = e.candidate_id) JOIN election el ON (el.election_id = ecc.election_id) WHERE experience_id NOT IN (SELECT ec.experience_id FROM experience_category ec) AND experience_id NOT IN (SELECT et.experience_id FROM experience_tag et) AND el.electionyear = 2016 GROUP by e.candidate_id HAVING COUNT (e.candidate_id) >5 ORDER BY COUNT (e.candidate_id) desc
"Present" Candidate Span - Finds any candidate experience entry that has "present" in the span (We add the candidate experience entry after they lose, so present should never be in the span). Written for the 2016 election, alter el.electionyear for future elections:
SELECT c.candidate_id, ex.title, ex.organization, ex.span FROM experience ex JOIN candidate c On (c.candidate_id = ex.candidate_id) JOIN election_candidate ec ON (ec.candidate_id = c.candidate_id) JOIN election el USING (election_id) WHERE experiencetype_id = 'P' AND ex.title iLIKE 'candidate' AND ex.span iLike '%present%' ORDER BY candidate_id
Incumbent Political Experience - Finds any incumbent that is currently holding a congressional or state legislative office but has no political experience entry pertaining to that office. Written for the 2016 election, alter el.electionyear for future elections:
SELECT oc.candidate_id, o.name, ec.state_id, o.title, oc.firstelect, dn.name AS district FROM office_candidate oc JOIN election_candidate ec ON (ec.candidate_id = oc.candidate_id) JOIN election el ON (el.election_id = ec.election_id) JOIN office o ON (o.office_id = oc.office_id) JOIN districtname dn ON (dn.districtname_id = oc.districtname_id) WHERE oc.candidate_id NOT IN (SELECT candidate_id FROM experience WHERE title SIMILAR TO '%(Representative|Senator|Delegate|Assembly Member%)' AND experiencetype_id = 'P') AND oc.officecandidatestatus_id = 1 AND o.officetype_id SIMILAR TO '%(C|L)%' AND el.electionyear = 2016 ORDER BY oc.state_id, oc.candidate_id, o.name
Committee Membership - Finds any ex-office holders that still hold committee positions in the "offices" tab. Remember to add the former committee membership to the "caucus" experiences before deleting them from the "offices" tab:
SELECT c.candidate_id, com.name, com.state_id, com.committeetype_id FROM committee com JOIN committee_candidate cc ON (cc.committee_id = com.committee_id) JOIN office_candidate oc ON (oc.office_candidate_id = cc.office_candidate_id) JOIN candidate c ON (c.candidate_id = oc.candidate_id) WHERE oc.officecandidatestatus_id = '0' GROUP by com.state_id, c.candidate_id, com.name, com.committeetype_id ORDER by com.state_id
Candidates Running in the General who were not in the Primary: - Returns a list of candidates who advanced from the primary elections (this query is useful following primary election candidate research to narrow down tracking sheets):
(SELECT DISTINCT(ec.candidate_id), c.lastname, c.firstname, ec.districtname_id, ec.state_id, p.name, ec.office_id FROM election e JOIN election_electionstage ee ON e.election_id = ee.election_id JOIN electionstage_candidate eec ON ee.election_electionstage_id = eec.election_electionstage_id JOIN election_candidate ec ON eec.election_candidate_id = ec.election_candidate_id JOIN candidate c ON ec.candidate_id = c.candidate_id JOIN electionstage_candidate_party ecp ON eec.electionstage_candidate_id = ecp.electionstage_candidate_id JOIN party p ON ecp.party_id = p.party_id WHERE ec.state_id = 'MS' AND ee.electionstage_id = 'G' AND electioncandidatestatus_id = 8 AND ec.office_id IN (7,8,9) AND e.special = 'False' and e.electionyear = '2019' Order BY ec.districtname_id, c.lastname) EXCEPT (SELECT DISTINCT(ec.candidate_id), c.lastname, c.firstname, ec.districtname_id, ec.state_id, p.name, ec.office_id FROM election e JOIN election_electionstage ee ON e.election_id = ee.election_id JOIN electionstage_candidate eec ON ee.election_electionstage_id = eec.election_electionstage_id JOIN election_candidate ec ON eec.election_candidate_id = ec.election_candidate_id JOIN candidate c ON ec.candidate_id = c.candidate_id JOIN electionstage_candidate_party ecp ON eec.electionstage_candidate_id = ecp.electionstage_candidate_id JOIN party p ON ecp.party_id = p.party_id WHERE ec.state_id = 'MS' AND ee.electionstage_id = 'P' AND ec.office_id IN (7,8,9) AND e.special = 'False' and e.electionyear = '2019' Order BY ec.districtname_id, c.lastname)
'Present' capitalized in span:
SELECT distinct c.candidate_id, firstname, lastname
FROM candidate c
JOIN experience e using (candidate_id)
JOIN office_candidate using (candidate_id)
WHERE span in ('Present', '%-Present') and officecandidatestatus_id = '1'
ORDER BY c.candidate_idEducational experiences that don't have a school:
SELECT DISTINCT (c.candidate_id), o.name, c.lastname, c.firstname, c.state_id, e.degree, e.school FROM candidate c JOIN education e ON c.candidate_id = e.candidate_id JOIN office_candidate oc ON c.candidate_id = oc.candidate_id JOIN office o ON oc.office_id = o.office_id WHERE o.officetype_id = 'L' AND e.degree IS NOT NULL AND e.school IS NULL ORDER BY o.name, c.state_id, c.lastname ASC
officecandidatestatus is elected:
SELECT c.candidate_id, c.firstname, c.lastname FROM candidate c JOIN office_candidate oc USING (candidate_id) WHERE officecandidatestatus_id = '3' ORDER BY c.candidate_id
Span contains spaces:
SELECT c.candidate_id, firstname, lastname FROM candidate c JOIN experience e USING (candidate_id) WHERE span LIKE '% - %'
Title field has a gendered title:
SELECT c.candidate_id, c.firstname, c.lastname FROM candidate c JOIN experience e USING (candidate_id) WHERE e.title LIKE '%man%' AND candidatestatus_id = '1'
