===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
- Enter the featured_id, corresponding to featured issue that you are interested in, in the WHERE clause - the example's featured_id = 19 (Russian Election Interference)
	- This filters the data to only pull speeches relating to a specific featured issue
- The string_agg(x.tag_name, ',') command is telling the computer to pull any additional tags these speeches have from the featured issue's tag association list
	- 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
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------