Revision history for OnPointQueries
Additions:
SELECT x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span, string_agg(x.tag_name, ',') AS tag_id
GROUP BY x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span
ORDER BY span desc, tag_id
GROUP BY x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span
ORDER BY span desc, tag_id
Deletions:
GROUP BY x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span, tag_name
ORDER BY span desc, tag_name
Additions:
SELECT x.speech_id, x.title, x.speechdate, string_agg(x.tag_name, ',') AS tag_name
ORDER BY tag_name
SELECT x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span, string_agg(x.tag_name, ',') As tag_name
GROUP BY x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span, tag_name
ORDER BY span desc, tag_name
ORDER BY tag_name
SELECT x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span, string_agg(x.tag_name, ',') As tag_name
GROUP BY x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span, tag_name
ORDER BY span desc, tag_name
Deletions:
ORDER BY tag_id
SELECT x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span, string_agg(x.tag_name, ',') AS tag_id
GROUP BY x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span
ORDER BY span desc, tag_id
Additions:
- 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)
Deletions:
Additions:
- **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.
Deletions:
Additions:
(SELECT (r.rating_id), state_id, s.name AS sig_name, r.name AS rating_name, r.span, t.name AS tag_name
JOIN rating_tag rt
JOIN tag t
WHERE ft.featured_id = 6 AND r.rating_id IN
GROUP BY x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span
JOIN rating_tag rt
JOIN tag t
WHERE ft.featured_id = 6 AND r.rating_id IN
GROUP BY x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span
Deletions:
JOIN sig_tag st
JOIN sig_category sc
LEFT JOIN rating_tag rt
LEFT JOIN rating_category rc
ON (rc.rating_id = r.rating_id)
WHERE r.rating_id IN
GROUP BY x.tag_id, x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span
Additions:
- 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**
Deletions:
Additions:
- This filters the data to only pull speeches relating to a specific featured issue
Deletions:
Additions:
- 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)
Deletions:
Additions:
- 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)
Deletions:
Additions:
- 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'
Deletions:
Additions:
- **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_id 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)
- 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
- This filters the data to specify you are only interested in 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)**
WHERE t.name = 'FBI-Director-removal')) x
How it Works:
- Enter a tag name in the WHERE clause (the query will therefore display all speeches tagged with the tag in question)
- 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
- This filters the data to specify you are only interested in 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)**
WHERE t.name = 'FBI-Director-removal')) x
Deletions:
WHERE tag_id = 112445)) x
Additions:
- **This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter a tag_id in the WHERE clause, and it displays all of that speeches with that tag. The query also displays any other tags from the issue's tag association list that the speeches are also tagged with. This is helpful in deciding to add a tag to an association list because it shows what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
Deletions:
Additions:
- **This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter a tag_id in the WHERE clause, and it displays all of that speeches with that tag. The query also displays any other tags from the issue's tag association list that the speeches are also tagged with. This is helpful in deciding to add a tag to an association list because ti shows what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
Deletions:
Additions:
- **This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the WHERE clause, and it displays all of that speeches with the tag. The query also displays any other tags from the issue's tag association list that the speeches are tagged with. This is helpful in deciding to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
Deletions:
Additions:
- **This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the WHERE clause, and it displays all of the speeches with the tag. The query also displays any other tags from the issue's tag association list that the speeches are tagged with. This is helpful in deciding to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
Deletions:
Additions:
- **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:**
Deletions:
Additions:
- **This query counts the amount of speeches a tag is used in, and you filter using a category (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
JOIN speech_category sc USING (speech_id)
JOIN category c USING (category_id)
JOIN speech_tag st USING (speech_id)
WHERE c.name = 'Guns'
GROUP BY c.name, t.tag_id, t.name
ORDER BY count(t.tag_id) desc
SELECT c.name, t.tag_id, t.name, count(t.tag_id) AS count
JOIN speech_category sc USING (speech_id)
JOIN category c USING (category_id)
JOIN speech_tag st USING (speech_id)
WHERE c.name = 'Guns'
GROUP BY c.name, t.tag_id, t.name
ORDER BY count(t.tag_id) desc
Additions:
- **This query displays all ballot measures that are currently live on the app (Helpful to make sure they all have summaries. measure text...ect):**
Deletions:
Additions:
- **This query displays all ballot measures that are currently live on the app:**
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 is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the WHERE clause, and it shows you all of the speeches tagged with the tag in question. The query also displays any other tags from the issue's tag association list that the speeches are tagged with. This is helpful in deciding to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
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 is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the WHERE clause, and it shows you all of the speeches tagged with the tag in question. The query also displays any other tags from the issue's tag association list that the speeches are tagged with. This is helpful in deciding to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
Deletions:
No Differences
Additions:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- **This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the WHERE clause, and it shows you all of the speeches tagged with the tag in question. The query also displays any other tags from the issue's tag association list that the speeches are tagged with. This is helpful in deciding to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
Deletions:
Additions:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- **This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the WHERE clause, and it shows you all of the speeches tagged with the tag in question. The query also displays any other tags from the issue's tag association list that the speeches are tagged with. This is helpful for looking to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
Deletions:
Additions:
- **This query does the same thing as the one above, but for rating scorecards instead of speeches**
Deletions:
Additions:
- **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):**
Deletions:
Additions:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- **This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the WHERE clause, and it shows you all of the speeches tagged with the tag in question. The query also displays any other tags from the issue's tag association list that the speeches have. This is helpful for looking to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
Deletions:
Additions:
- **This query does the same thing as the one above, but for rating scorecards instead of tags**
Deletions:
Additions:
- **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):**
- **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:**
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- **This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the where clause, and it shows you all of the speeches tagged with the tag in question. The query also displays any other tags from the issue's tag association list that the speeches have. This is helpful for looking to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
- **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:**
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- **This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the where clause, and it shows you all of the speeches tagged with the tag in question. The query also displays any other tags from the issue's tag association list that the speeches have. This is helpful for looking to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.**
Deletions:
- 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):
- 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:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the where clause, and it shows you all of the speeches tagged with the tag in question. The query also displays any other tags from the issue's tag association list that the speeches have. This is helpful for looking to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.
Deletions:
Additions:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- This query is helpful in deciding whether to cut or add a tag to a featured issue's tag association list. Enter the tag_id in the where clause, and it shows you all of the speeches tagged with the tag in question. The query also displays any other tags from the issue's tag association list that the speeches have. This is helpful for looking to add a tag to an association list because you will see what speeches will be pulled from that specific tag (they are the ones with only that tag, not another one from the tag association list). Likewise, when deciding to cut a tag from an association list, it shows which speeches will no longer pull for the issue in OnPoint.
SELECT x.speech_id, x.title, x.speechdate, string_agg(x.tag_name, ',') AS tag_id
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 tag_id = 112445)) x
GROUP BY x.speech_id, x.title, x.speechdate
ORDER BY tag_id
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- This query does the same thing as the one above, but for rating scorecards instead of tags
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, rt.tag_id, t.name AS tag_name
FROM sig s
JOIN rating r
USING (sig_id)
JOIN sig_tag st
USING (sig_id)
JOIN sig_category sc
USING (sig_id)
LEFT JOIN rating_tag rt
ON (rt.rating_id = r.rating_id)
LEFT JOIN rating_category rc
ON (rc.rating_id = r.rating_id)
LEFT JOIN tag t
ON (t.tag_id = rt.tag_id)
JOIN featured_tag ft
ON t.tag_id = ft.tag_id
WHERE 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.tag_id, x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span
ORDER BY span desc, tag_id
_________________________________________________________________________________________________________________________________________________________________________________________________
SELECT x.speech_id, x.title, x.speechdate, string_agg(x.tag_name, ',') AS tag_id
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 tag_id = 112445)) x
GROUP BY x.speech_id, x.title, x.speechdate
ORDER BY tag_id
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- This query does the same thing as the one above, but for rating scorecards instead of tags
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, rt.tag_id, t.name AS tag_name
FROM sig s
JOIN rating r
USING (sig_id)
JOIN sig_tag st
USING (sig_id)
JOIN sig_category sc
USING (sig_id)
LEFT JOIN rating_tag rt
ON (rt.rating_id = r.rating_id)
LEFT JOIN rating_category rc
ON (rc.rating_id = r.rating_id)
LEFT JOIN tag t
ON (t.tag_id = rt.tag_id)
JOIN featured_tag ft
ON t.tag_id = ft.tag_id
WHERE 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.tag_id, x.rating_id, x.state_id, x.sig_name, x.rating_name, x.span
ORDER BY span desc, tag_id
_________________________________________________________________________________________________________________________________________________________________________________________________
Deletions:
No Differences
Additions:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Adding a WHERE featured_id = '#' will display all the tags for a featured issue.
Adding a WHERE featured_id = '#' will display all the tags for a featured issue.
Deletions:
Additions:
- 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:
Deletions:
Additions:
- This query displays the **featured_tag table**, as well as the name of the tag. This is helpful to find the list of tag_id's associated with a featured_id:
Deletions:
Additions:
**Simple Queries for Viewing OnPoint Info**:
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.
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.
Deletions:
SELECT *
FROM featured_tag
Additions:
- 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):
- This query displays the **featured_tag table**. This is helpful to find the list of tag_id's associated with a featured_id:
SELECT *
FROM featured_tag
- This query displays the **featured_tag table**. This is helpful to find the list of tag_id's associated with a featured_id:
SELECT *
FROM featured_tag
Deletions:
Additions:
**Simple Queries for Viewing OnPoint Tables**:
- 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
