Follower demographics

1-- core.linkedin_pages.follower_demographics
2-- Who follows each LinkedIn Page: the follower breakdowns from the Followers
3-- section of Page analytics (seniority, job function, industry, country,
4-- location, company size, employees), in one long table.
5--
6-- Grain: organization_id x facet x segment_id. A snapshot as of the last sync.
7-- Depends on: staging.linkedin_pages.followers_by_*, .total_follower_count
8--
9-- LinkedIn returns at most the top 100 segments per facet and leaves members who
10-- did not fill in a field out of that facet, so a facet's segments rarely add up
11-- to the Page's follower total. share_of_facet divides by the facet's own sum;
12-- share_of_followers divides by the Page's total, so the gap shows what is not
13-- covered. The tables are replaced on every sync, so there is no history: to
14-- track demographics over time, snapshot this model on a schedule.
15
16WITH segments AS (
17    SELECT * FROM {{staging.linkedin_pages.followers_by_seniority}}
18    UNION ALL
19    SELECT * FROM {{staging.linkedin_pages.followers_by_function}}
20    UNION ALL
21    SELECT * FROM {{staging.linkedin_pages.followers_by_industry}}
22    UNION ALL
23    SELECT * FROM {{staging.linkedin_pages.followers_by_country}}
24    UNION ALL
25    SELECT * FROM {{staging.linkedin_pages.followers_by_location}}
26    UNION ALL
27    SELECT * FROM {{staging.linkedin_pages.followers_by_company_size}}
28    UNION ALL
29    SELECT * FROM {{staging.linkedin_pages.followers_by_association_type}}
30)
31
32SELECT
33    s.organization_id,
34    t.organization_name,
35    s.facet,
36    s.segment_id,
37    s.segment_name,
38    s.followers,
39    SAFE_DIVIDE(s.followers, SUM(s.followers) OVER (PARTITION BY s.organization_id, s.facet)) AS share_of_facet,
40    SAFE_DIVIDE(s.followers, t.follower_count)                                                AS share_of_followers,