Helpful OnPoint Queries
- This query displays the featured table. This is helpful to find featured_id, the official issue title, issue descriptions, and release_id (1 = unreleased; 2 = released):
SELECT *
FROM featured
- This query displays the featured_tag table, as well as the name of the tag. This is helpful to see the list of tags associated with a featured_id:
SELECT ft.*, t.name
FROM featured_tag ft
JOIN tag t ON ft.tag_id = t.tag_id
Adding a WHERE featured_id = '#' will display all the tags for a featured issue.
- This query displays all ballot measures that are currently live on the app (Helpful to make sure they all have summaries. measure text...ect):
SELECT m.*
FROM featured f
JOIN featured_tag ft USING (featured_id)
JOIN tag t USING (tag_id)
JOIN measure_tag mt USING (tag_id)
JOIN measure m USING (measure_id)
WHERE f.release_id = 2
ORDER BY electiondate desc, state_id
- This query counts the amount of speeches a tag is used in, and you filter using a category name (right now it is set to 'Guns'). This is helpful because it shows you the scope of what data will be pulled if you add a tag to a tag association list:
SELECT c.name, t.tag_id, t.name, count(t.tag_id) AS count
FROM speech s
JOIN speech_category sc USING (speech_id)
JOIN category c USING (category_id)
JOIN speech_tag st USING (speech_id)
JOIN tag t USING (tag_id)
WHERE c.name = 'Guns'
GROUP BY c.name, t.tag_id, t.name
ORDER BY count(t.tag_id) desc
- This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. It displays what speeches will be pulled from that specific tag. Additionally, it displays what else those speeches are tagged with - specifically other tags that are on the featured issue's tag association list. This allows the researcher to narrow down what speeches a specific tag will actually add to the featured issue in the app - the speeches that are tagged with the tag in question, and no other tags on the tag association list.
How it Works:
- Enter a tag name in the WHERE clause (the query will therefore display all speeches tagged with the tag in question) - the example's tag in question is 'FBI-Director-removal'
- This pulls the speech_id, title and speechdate for all of these speeches
- This filters the data to only pull speeches relating to a specific featured issue
- This shows you if the tag you are interested in is the reason a speech will pull - if it's not accompanied by another tag on the tag association list
SELECT x.speech_id, x.title, x.speechdate, string_agg(x.tag_name, ',') AS tag_name
FROM
(SELECT (s.speech_id), s.title, s.speechdate, st.tag_id, t.name AS tag_name
FROM speech s
LEFT JOIN speech_tag st
ON (s.speech_id = st.speech_id)
LEFT JOIN tag t
ON (t.tag_id = st.tag_id)
LEFT JOIN featured_tag ft
ON (t.tag_id = ft.tag_id)
WHERE ft.featured_id = 19
AND s.speech_id IN
(SELECT speech_id
FROM tag t
JOIN speech_tag st USING (tag_id)
JOIN speech s USING (speech_id)
WHERE t.name = 'FBI-Director-removal')) x
GROUP BY x.speech_id, x.title, x.speechdate
ORDER BY tag_name
- This query does the same thing as the one above, but for rating scorecards instead of speeches
SELECT x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span, string_agg(x.tag_name, ',') AS tag_id
FROM
(SELECT (r.rating_id), state_id, s.name AS sig_name, r.name AS rating_name, r.span, t.name AS tag_name
FROM sig s
JOIN rating r
USING (sig_id)
JOIN rating_tag rt
ON (rt.rating_id = r.rating_id)
JOIN tag t
ON (t.tag_id = rt.tag_id)
JOIN featured_tag ft
ON t.tag_id = ft.tag_id
WHERE ft.featured_id = 6 AND r.rating_id IN
(SELECT rating_id
FROM tag t
JOIN rating_tag rt USING (tag_id)
JOIN rating r USING (rating_id)
WHERE tag_id = 338)) x
GROUP BY x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span
ORDER BY span desc, tag_id
