Last active
November 4, 2021 13:15
-
-
Save morsapaes/1df1ded4aefe27485b83c2333e31c944 to your computer and use it in GitHub Desktop.
Analyzing Twitch streams with Materialize: what are the most used tags?
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE MATERIALIZED VIEW mv_agg_stream_tag AS | |
SELECT st.localization_name AS tag, | |
cnt_tag | |
FROM ( | |
--Expand the tags array for each event | |
--and aggregate before joining | |
SELECT tg, COUNT(*) AS cnt_tag | |
FROM v_twitch_stream ts, | |
unnest(tag_ids) tg | |
WHERE game_id IS NOT NULL | |
GROUP BY tg | |
) un | |
JOIN stream_tag_ids st ON un.tg = st.tag_id AND NOT st.is_auto; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment