Revision [18718]
This is an old revision of ProfilesErrorQueries made by jamiem on 2016-11-23 12:14:33.
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
Issue Positions - This query finds all speeches that have been marked as speechtype 7 with Issue Position either spelled wrong or missing since December 31, 2013. This query often times shows real speeches that were accidentally marked as Issue Positions. Send this list to the head of the speeches department so that they can fix them:
SELECT c.candidate_id, c.lastname, s.title, s.speechdate FROM candidate c JOIN speech_candidate sc USING (candidate_id) JOIN speech s USING (speech_id) WHERE s.speechtype_id = '7' AND s.title NOT LIKE '%Issue Position%' AND s.speechdate > '%12/31/2013%' 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
