Skip to content

Instantly share code, notes, and snippets.

@morsapaes
Last active November 4, 2021 13:15
Show Gist options
  • Save morsapaes/1df1ded4aefe27485b83c2333e31c944 to your computer and use it in GitHub Desktop.
Save morsapaes/1df1ded4aefe27485b83c2333e31c944 to your computer and use it in GitHub Desktop.
Analyzing Twitch streams with Materialize: what are the most used tags?
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