Update Tables (Statuses Most Retweeted by Bots)

[s2t2]

Instructions

Update the data used in the StatusesMostRetweeted tables. Use this query instead (once for opinion community 0, then again for opinion community 1:

WITH bot_rts as (
    SELECT 
      t.status_id
      ,t.user_id
      ,t.status_text
      ,t.retweet_status_id
    -- FROM `tweet-collector-py.impeachment_production.tweets_v2` t
    -- JOIN `tweet-collector-py.impeachment_production.user_details_v20210806_slim` u on u.user_id = t.user_id
    FROM `tweet-research-shared.impeachment_2020.tweets_v2` t
    JOIN `tweet-research-shared.impeachment_2020.user_details_v20210806_slim` u on u.user_id = t.user_id
    WHERE u.is_bot and t.retweet_status_id IS NOT NULL
        and u.opinion_community=1 -- use 0 or 1
    --LIMIT 10
)

-- SELECT
--  count(distinct retweet_status_id) -- 1,143,473 original tweets
-- ,count(distinct status_id) -- 20,120,993 bot RTs
-- FROM bot_rts

SELECT 
  status_text
  ,count(distinct status_id) as rt_count
  ,count(distinct user_id) as user_count
FROM bot_rts
GROUP BY 1
ORDER BY 3 DESC
LIMIT 20

Convert the results of each query to JSON. Then update the src/data/bot_opinion_communities/statuses_most_retweeted.js file with the new data.

Acceptance Criteria

Please post a screenshot of the updated tables from a locally running instance of this app.

[s2t2]

I just realized these are / were bot counts. The current tables are right.