CREATE TRANSIENT TABLE agg_user_platform_daily (
    user_id                 NUMBER FOREIGN KEY REFERENCES suno_prod.prod.dim_user(user_id) COMMENT 'User ID (number) from dim_user table'
    , user_uid               VARCHAR(255) FOREIGN KEY REFERENCES suno_prod.prod.dim_user(user_uid) COMMENT 'User UID (string) from dim_user table'
    , subscription_tier      VARCHAR(255) COMMENT 'Subscription tier on the day of execution; null if the user is not a paid subscriber. Incorrect value if this is backfilled on a later date'
    , platform              VARCHAR(255) COMMENT 'Platform (web/ios/android)'
    , clip_total_cnt        NUMBER COMMENT 'Total number of clips (of all types, e.g. gen/upload/extend/concat/etc) generated on this platform'
    , gen_clip_total_cnt    NUMBER COMMENT 'Total number of clips generated (generation type only) on this platform'
    , play_cnt              NUMBER COMMENT 'Total number of clips (user-owned and other users) played on this platform (using play_duration_threshold = 5)'
    , play_cnt_own          NUMBER COMMENT 'Number of user-owned clips played on this platform (using play_duration_threshold = 5)'
    , play_cnt_others       NUMBER COMMENT 'Number of other users clips played on this platform (using play_duration_threshold = 5)'
    , play_duration_sec     FLOAT COMMENT 'Total play duration in seconds on this platform (using play_duration_threshold = 5)'
    , play_duration_sec_own FLOAT COMMENT 'Play duration in seconds of user-owned clips on this platform (using play_duration_threshold = 5)'
    , play_duration_sec_others FLOAT COMMENT 'Play duration in seconds of other users clips on this platform (using play_duration_threshold = 5)'
    , total_follower_cnt    NUMBER COMMENT 'Total number of followers on this day, platform-agnostic'
    , total_following_cnt   NUMBER COMMENT 'Total number of following on this day, platform-agnostic'
    , net_likes_received_cnt NUMBER COMMENT 'Total number of likes received on this day, platform-agnostic'
    , is_active             BOOLEAN COMMENT 'Whether the user is active (generated 1+ clips or played 1+ clips) on this day on this platform. Should be true for all rows since we only include active user-days in this table'
    , is_active_create      BOOLEAN COMMENT 'Whether the user is an active creator (generated 1+ clips) on this day on this platform'
    , is_active_listen      BOOLEAN COMMENT 'Whether the user is an active listener (played 1+ clips) on this day on this platform'
    , is_active_listen_own  BOOLEAN COMMENT 'Whether the user is an active listener of their own clips (played 1+ clips) on this day on this platform'
    , is_active_listen_others BOOLEAN COMMENT 'Whether the user is an active listener of other users clips (played 1+ clips) on this day on this platform'
    , is_prev_day_active    BOOLEAN COMMENT 'Whether the user was active on the previous day (D-1) on this platform'
    , is_prev_day_active_create BOOLEAN COMMENT 'Whether the user was an active creator on the previous day (D-1) on this platform'
    , is_prev_day_active_listen BOOLEAN COMMENT 'Whether the user was an active listener on the previous day (D-1) on this platform'
    , is_prev_day_active_listen_own BOOLEAN COMMENT 'Whether the user was an active listener of their own clips on the previous day (D-1) on this platform'
    , is_prev_day_active_listen_others BOOLEAN COMMENT 'Whether the user was an active listener of other users clips on the previous day (D-1) on this platform'
    , is_7d_active          BOOLEAN COMMENT 'Whether the user is active during the 7-day window up to and including this day (D-6 to D) on this platform'
    , is_7d_active_create   BOOLEAN COMMENT 'Whether the user is an active creator during the 7-day window up to and including this day (D-6 to D) on this platform'
    , is_7d_active_listen   BOOLEAN COMMENT 'Whether the user is an active listener during the 7-day window up to and including this day (D-6 to D) on this platform'
    , is_7d_active_listen_own BOOLEAN COMMENT 'Whether the user is an active listener of their own clips during the 7-day window up to and including this day (D-6 to D) on this platform'
    , is_7d_active_listen_others BOOLEAN COMMENT 'Whether the user is an active listener of other users clips during the 7-day window up to and including this day (D-6 to D) on this platform'
    , is_prev_7d_active     BOOLEAN COMMENT 'Whether the user was active during the previous 7-day window (D-13 to D-7) on this platform'
    , is_prev_7d_active_create BOOLEAN COMMENT 'Whether the user was an active creator during the previous 7-day window (D-13 to D-7) on this platform'
    , is_prev_7d_active_listen BOOLEAN COMMENT 'Whether the user was an active listener during the previous 7-day window (D-13 to D-7) on this platform'
    , is_prev_7d_active_listen_own BOOLEAN COMMENT 'Whether the user was an active listener of their own clips during the previous 7-day window (D-13 to D-7) on this platform'
    , is_prev_7d_active_listen_others BOOLEAN COMMENT 'Whether the user was an active listener of other users clips during the previous 7-day window (D-13 to D-7) on this platform'
    , is_28d_active         BOOLEAN COMMENT 'Whether the user is active during the 28-day window up to and including this day (D-27 to D) on this platform'
    , is_28d_active_create  BOOLEAN COMMENT 'Whether the user is an active creator during the 28-day window up to and including this day (D-27 to D) on this platform'
    , is_28d_active_listen  BOOLEAN COMMENT 'Whether the user is an active listener during the 28-day window up to and including this day (D-27 to D) on this platform'
    , is_28d_active_listen_own BOOLEAN COMMENT 'Whether the user is an active listener of their own clips during the 28-day window up to and including this day (D-27 to D) on this platform'
    , is_28d_active_listen_others BOOLEAN COMMENT 'Whether the user is an active listener of other users clips during the 28-day window up to and including this day (D-27 to D) on this platform'
    , is_prev_28d_active    BOOLEAN COMMENT 'Whether the user was active during the previous 28-day window (D-55 to D-28) on this platform'
    , is_prev_28d_active_create BOOLEAN COMMENT 'Whether the user was an active creator during the previous 28-day window (D-55 to D-28) on this platform'
    , is_prev_28d_active_listen BOOLEAN COMMENT 'Whether the user was an active listener during the previous 28-day window (D-55 to D-28) on this platform'
    , is_prev_28d_active_listen_own BOOLEAN COMMENT 'Whether the user was an active listener of their own clips during the previous 28-day window (D-55 to D-28) on this platform'
    , is_prev_28d_active_listen_others BOOLEAN COMMENT 'Whether the user was an active listener of other users clips during the previous 28-day window (D-55 to D-28) on this platform'
    , p_date                DATE
    , days_since_user_joined NUMBER COMMENT 'UTC days since user joined, join date is day 0'
) CLUSTER BY (p_date)
COMMENT = 'Aggregate daily user metrics by platform, for only dates when the user is active';
