Queries for Special Interest Groups project: PVSWiki

Queries for Special Interest Groups project

Pyquals and Errors

Orphan SIGs

SELECT s.sig_id, s.name
FROM sig s
LEFT JOIN rating r USING (sig_id)
LEFT JOIN endorse e USING (sig_id)
WHERE r.rating_id IS NULL AND e.endorse_id IS NULL;

Orphan Ratings

SELECT r.rating_id, r.name
FROM rating r
LEFT JOIN rating_candidate rc USING (rating_id)
WHERE rc.rating_candidate_id IS NULL

SIGs without Categories

SELECT s.sig_id, s.name AS sig 
FROM sig s 
LEFT JOIN sig_category sc USING (sig_id) 
WHERE sc.category_id IS NULL;

Ratings Without Categories

SELECT r.rating_id, r.name AS rating, r.span, s.name AS sig
FROM rating r
JOIN sig s USING (sig_id)
LEFT JOIN rating_category rc USING (rating_id)
WHERE rc.rating_category_id IS NULL;

Creates spreadsheet of SIGs not released to Live Web yet for whatever reason

SELECT sig_id, state_id, name, release_id 
FROM sig
WHERE release_id != '1' ;

Finds ratings that are live yet empty

SELECT s.sig_id, s.name, r.rating_id, r.name, r.span
FROM sig s JOIN rating r ON s.sig_id = r.sig_id JOIN rating_candidate rc ON r.rating_id = rc.rating_id
WHERE r.release_id = 2 AND rc.sig_rating = ''
GROUP BY s.sig_id, r.rating_id, rc.sig_rating ;

The query below shows candidates which are running/have ran in an election year given officetype_id, and electionyear. For this particular query, we looked at Congressional candidates in YYYY. This query is useful for candidate matching to generate the index-match worksheet for groups that rate candidates prior to elections (ex. Voter Guides)

SELECT ec2.electionyear, ec2.candidate_id, ec2.lastname, ec2.firstname, ec2.middlename, ec2.nickname, ec2.suffix, ec2.office, ec2.state_id, dn.name district FROM
 (SELECT e.electionyear, ec.candidate_id, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, ec.office_id, o.name office, ec.state_id, ec.districtname_id 
FROM election e JOIN election_candidate ec USING (election_id) JOIN candidate c USING (candidate_id) JOIN office o USING (office_id) 
WHERE e.officetype_id = 'C' AND e.electionyear = 'YYYY' ORDER BY o.name, ec.state_id, ec.districtname_id) ec2 
LEFT JOIN districtname dn USING (districtname_id) 
GROUP BY ec2.electionyear, ec2.candidate_id, ec2.lastname, ec2.firstname, ec2.middlename, ec2.nickname, ec2.suffix, ec2.office, ec2.state_id, dn.districtname_id, dn.name 
ORDER BY ec2.state_id, ec2.office DESC, dn.districtname_id ;

The query below is useful for displaying multiple rating_id's in one resulting returned dataset as long as every official has a score. This particular example shows the Progressive and Regressive scores for the Progressive Patriots/That's My Congress/Irregular Times scorecard. Each official will have one of each score (as well as a Net Score which is not shown in the query below). You can add extra fields to the query as needed (ie. lastname, firstname, office, etc.)

SELECT rc1.candidate_id, rc1.sig_rating s_pro, rc1.our_rating o_pro, rc2.sig_rating s_reg, rc2.our_rating o_reg
FROM rating_candidate rc1
JOIN rating_candidate rc2 USING (candidate_id)
WHERE rc1.rating_id = 6582 AND rc2.rating_id = 6583 ;

The query below will help us construct our worksheet for the index/match function in spreadsheets. The example shows all officials who have ever served at the state legislature level in California.

SELECT oc.candidate_id, '' matchkey, '' checks, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, o.name office, oc.state_id, oc.districtname_id, dn.name district
FROM office o
LEFT JOIN office_candidate oc USING (office_id)
JOIN candidate c USING (candidate_id)
JOIN districtname dn USING (districtname_id)
WHERE o.officetype_id = 'L' AND oc.state_id = 'CA'
ORDER BY o.name DESC, oc.districtname_id ;

This query will help for webchecks of a certain rating_id

SELECT c.firstname, c.lastname, rc.sig_rating, rc.our_rating, o.name, c.state_id, oc.districtname_id
FROM candidate c JOIN  rating_candidate rc ON c.candidate_id = rc.candidate_id
JOIN office_candidate oc ON c.candidate_id = oc.candidate_id
JOIN office o ON oc.office_id = o.office_id
WHERE rating_id = 6835 AND o.officetype_id = 'C'
GROUP BY c.firstname, c.lastname, rc.sig_rating, o.name, c.state_id, rc.our_rating, oc.districtname_id
ORDER BY c.state_id, oc.districtname_id ;

Brings up how many speeches mention the word "endorse" in database

SELECT s.title, s.speechdate 
FROM speech s 
WHERE (s.created BETWEEN '08/01/14' AND '08/13/14') AND s.title LIKE '%endorse%' OR s.speechtext LIKE '%endorse%' 
ORDER BY s.speechdate DESC ;

Finds candidate with most endorsements in year

SELECT c.firstname, c.lastname,COUNT (endorse_candidate_id) total_endorsements, c.state_id 
FROM election el JOIN election_candidate ec USING (election_id) 
JOIN candidate c USING (candidate_id) 
JOIN office_candidate oc USING (candidate_id) 
JOIN office o ON (oc.office_id = o.office_id) 
JOIN endorse_candidate elc USING (election_candidate_id) 
JOIN endorse e USING (endorse_id) 
JOIN sig sg USING (sig_id) JOIN sig_category sc USING (sig_id) 
JOIN category cat USING (category_id) 
WHERE el.electionyear = '2014' GROUP BY c.firstname, c.lastname, c.state_id ORDER BY total_endorsements DESC ;

Brings up SIGS in descending order of how many endorsements they gave per election year

SELECT COUNT (candidate_id) total_endorsements, e.sig_id, sg.name, el.electionyear 
FROM election el JOIN election_candidate ec USING (election_id) 
JOIN candidate c USING (candidate_id) 
JOIN office_candidate oc USING (candidate_id) 
JOIN office o ON (oc.office_id = o.office_id) 
JOIN endorse_candidate elc USING (election_candidate_id) 
JOIN endorse e USING (endorse_id) JOIN sig sg USING (sig_id) 
JOIN sig_category sc USING (sig_id) JOIN category cat USING (category_id) 
WHERE el.electionyear = '2014' AND sg.state_id = 'NA' AND o.officetype_id = 'C' 
GROUP BY e.sig_id, sg.name, el.electionyear 
ORDER BY total_endorsements DESC

PVSWiki : QueriesForRatings

Queries for Special Interest Groups project

Pyquals and Errors

Useful or Interesting Queries