Viewer demographics daily

1-- core.youtube_analytics.viewer_demographics_daily
2-- Who watches each channel: views split by age group and gender per day, the
3-- "Age" and "Gender" cards of the YouTube Studio Audience tab.
4--
5-- Grain: date_day x channel_id x age_group x gender. Dates are Pacific Time.
6-- Depends on: staging.youtube_analytics.channel_demographics, .channel_basic
7--
8-- YouTube delivers demographics as percentages per slice (video, country,
9-- subscribed status, live or on demand), not as counts. Adding those
10-- percentages up across slices gives a number with no meaning: a slice with
11-- 3 views would count as much as one with 30,000. Here each percentage is
12-- multiplied by the views of its own slice from the activity report, which
13-- turns it into estimated views; those can be summed and divided again.
14--
15-- estimated_views only covers signed-in viewers whose age and gender YouTube
16-- knows, and slices that YouTube withheld from either report for privacy drop
17-- out. Compare shares, not totals, with Studio, and re-derive the share from
18-- summed estimated_views for a longer period.
19
20WITH slice_views AS (
21    -- The activity report is already one row per slice; the SUM only guards
22    -- against a slice that arrives twice.
23    SELECT
24        date_day,
25        channel_id,
26        video_id,
27        live_or_on_demand,
28        subscribed_status,
29        country_code,
30        SUM(views) AS views
31    FROM {{staging.youtube_analytics.channel_basic}}
32    GROUP BY 1, 2, 3, 4, 5, 6
33),
34
35weighted AS (
36    SELECT
37        d.date_day,
38        d.channel_id,
39        d.age_group,
40        d.gender,