Analyzing Twitch streams with Materialize: who are the most popular streamers for each of the top10 games?

twitch_lateral_join.sql

--Use a view to create a "shortcut" 
--to the top10 games query
CREATE VIEW v_stream_game_top10 AS 
SELECT game_id, 
       game_name, 
       agg_viewer_cnt 
FROM mv_agg_stream_game 
ORDER BY agg_viewer_cnt DESC 
LIMIT 10;

CREATE MATERIALIZED VIEW mv_stream_game_top10 AS
SELECT t.game_name, 
       user_name, 
       sum_viewer_cnt
FROM v_stream_game_top10 t,
--For each game in the top10...
LATERAL (
          SELECT game_name, 
                 user_name, 
                 SUM(viewer_count) AS sum_viewer_cnt
          FROM v_twitch_stream ts
          --...use input as parameter to find the most
          --popular streamer!
          WHERE t.game_id = ts.game_id
            AND game_id IS NOT NULL
          GROUP BY game_name, 
                user_name
          ORDER BY sum_viewer_cnt DESC
          LIMIT 1
        );