Profile performance daily

1-- core.tiktok_organic.profile_performance_daily
2-- The TikTok Analytics Overview per account per day: followers, net follower
3-- change, profile views, video views, likes, comments, shares and engagement
4-- rate.
5--
6-- Grain: date_day x business_id. Days are UTC.
7-- Depends on: staging.tiktok_organic.profile_metrics, .profile
8--
9-- FOLLOWERS IS A RUNNING TOTAL: take the last day of a period, never the sum.
10-- follower_change is the difference from the previous day that has a follower
11-- count. A follower count of 0 is treated as "not yet processed" (TikTok
12-- reports unprocessed days as 0) and left NULL, so it does not show as a
13-- collapse and recovery.
14--
15-- engagement_rate = (likes + comments + shares) / video_views for the day. To
16-- roll up, divide summed engagements by summed video_views.
17
18WITH daily AS (
19    SELECT
20        date_day,
21        business_id,
22        NULLIF(followers, 0) AS followers,
23        profile_views,
24        video_views,
25        likes,
26        comments,
27        shares
28    FROM {{staging.tiktok_organic.profile_metrics}}
29    WHERE date_day IS NOT NULL
30)
31
32SELECT
33    d.date_day,
34    d.business_id,
35    p.username,
36    p.display_name,
37    d.followers,
38    d.followers - LAST_VALUE(d.followers IGNORE NULLS) OVER (
39        PARTITION BY d.business_id ORDER BY d.date_day
40        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING