Follower growth daily

1-- core.linkedin_pages.follower_growth_daily
2-- The Followers section of LinkedIn Page analytics: new followers per day, split
3-- organic and paid, with an estimated follower total for each day.
4--
5-- Grain: date_day x organization_id. Days are UTC.
6-- Depends on: staging.linkedin_pages.follower_gains_daily, .total_follower_count
7--
8-- LinkedIn gives daily gains and a current total, but no daily total. The total
9-- for each day is rebuilt backwards: today's count minus every gain after that
10-- day. That makes `followers_estimated` an estimate:
11--   - the current count is taken at sync time, while gains stop two days before
12--     today, so the last day already includes those one or two days of gains;
13--   - LinkedIn does not document whether gains are net of unfollows. If they are
14--     not, the estimate drifts above the true count the further back you go.
15-- Use it for the shape of the curve. Sum the gain columns for growth over a
16-- period; never sum followers_estimated.
17
18WITH gains AS (
19    SELECT
20        date_day,
21        organization_id,
22        organization_name,
23        organic_follower_gain,
24        paid_follower_gain,
25        organic_follower_gain + paid_follower_gain AS follower_gain
26    FROM {{staging.linkedin_pages.follower_gains_daily}}
27)
28
29SELECT
30    g.date_day,
31    g.organization_id,
32    g.organization_name,
33    g.organic_follower_gain,
34    g.paid_follower_gain,
35    g.follower_gain,
36    t.follower_count                                                  AS current_follower_count,
37    t.follower_count - COALESCE(SUM(g.follower_gain) OVER (
38        PARTITION BY g.organization_id
39        ORDER BY g.date_day
40        ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING