Channel performance daily

1-- core.youtube_analytics.channel_performance_daily
2-- Channel totals per day: views, watch time, engagement, subscriber movement and
3-- thumbnail reach. The YouTube Studio channel overview, by day.
4--
5-- Grain: date_day x channel_id. Dates are Pacific Time.
6-- Depends on: staging.youtube_analytics.channel_basic, core.youtube_analytics.video_performance_daily
7--
8-- Subscribers are read from the activity report directly, not summed from
9-- videos: YouTube puts subscriptions that happen off a watch page (channel page,
10-- home feed) on rows with no video, and those would be lost in a video roll-up.
11-- `subscribers_off_video` shows how many that is.
12
13WITH channel_subs AS (
14    SELECT
15        date_day,
16        channel_id,
17        SUM(subscribers_gained)                              AS subscribers_gained,
18        SUM(subscribers_lost)                                AS subscribers_lost,
19        SUM(IF(video_id IS NULL, subscribers_gained - subscribers_lost, 0)) AS subscribers_off_video
20    FROM {{staging.youtube_analytics.channel_basic}}
21    GROUP BY 1, 2
22),
23
24videos AS (
25    SELECT
26        date_day,
27        channel_id,
28        SUM(views)                      AS views,
29        SUM(engaged_views)              AS engaged_views,
30        SUM(premium_views)              AS premium_views,
31        SUM(live_views)                 AS live_views,
32        SUM(views_from_subscribers)     AS views_from_subscribers,
33        SUM(watch_time_minutes)         AS watch_time_minutes,
34        SUM(view_percentage_weighted)   AS view_percentage_weighted,
35        SUM(likes)                      AS likes,
36        SUM(dislikes)                   AS dislikes,
37        SUM(comments)                   AS comments,
38        SUM(shares)                     AS shares,
39        SUM(thumbnail_impressions)      AS thumbnail_impressions,
40        SUM(thumbnail_ctr_weighted)     AS thumbnail_ctr_weighted,