{{
    config(
        materialized='table'
        , snowflake_warehouse='DBT_DEV_LARGE'
    )
}}

SELECT
    user_id
    , user_uid
    , COUNT_IF(is_active) AS n_days_active
    , COUNT_IF(is_active_create) AS n_days_active_create
    , COUNT_IF(is_active_listen) AS n_days_active_listen
    , COUNT_IF(is_active AND p_date >= CURRENT_DATE() - INTERVAL '7 days') AS n_days_active_last_7_days
    , COUNT_IF(is_active_create AND p_date >= CURRENT_DATE() - INTERVAL '7 days') AS n_days_active_create_last_7_days
    , COUNT_IF(is_active_listen AND p_date >= CURRENT_DATE() - INTERVAL '7 days') AS n_days_active_listen_last_7_days
    , COUNT_IF(is_active_listen_own AND p_date >= CURRENT_DATE() - INTERVAL '7 days') AS n_days_active_listen_own_last_7_days
    , COUNT_IF(is_active_listen_others AND p_date >= CURRENT_DATE() - INTERVAL '7 days') AS n_days_active_listen_others_last_7_days
    , COUNT_IF(is_active AND p_date >= CURRENT_DATE() - INTERVAL '28 days') AS n_days_active_last_28_days
    , COUNT_IF(is_active_create AND p_date >= CURRENT_DATE() - INTERVAL '28 days') AS n_days_active_create_last_28_days
    , COUNT_IF(is_active_listen AND p_date >= CURRENT_DATE() - INTERVAL '28 days') AS n_days_active_listen_last_28_days
    , COUNT_IF(is_active_listen_own AND p_date >= CURRENT_DATE() - INTERVAL '28 days') AS n_days_active_listen_own_last_28_days
    , COUNT_IF(is_active_listen_others AND p_date >= CURRENT_DATE() - INTERVAL '28 days') AS n_days_active_listen_others_last_28_days
    , SUM(clip_total_cnt) AS n_clips_created
    , SUM(gen_clip_total_cnt) AS n_gen_clips_created
    , DIV0NULL(SUM(gen_clip_total_cnt), SUM(clip_total_cnt)) AS pct_clips_created_gen
    , DIV0NULL(SUM(clip_total_cnt), COUNT_IF(is_active_create)) AS avg_clips_created_per_active_create_day
    , SUM(play_cnt) AS n_plays
    , SUM(play_cnt_own) AS n_plays_own
    , SUM(play_cnt_others) AS n_plays_others
    , DIV0NULL(SUM(play_cnt_own), SUM(play_cnt)) AS pct_plays_own
    , DIV0NULL(SUM(play_cnt_others), SUM(play_cnt_own)) AS ratio_plays_others_to_own
    , DIV0NULL(SUM(play_cnt), COUNT_IF(is_active_listen)) AS avg_plays_per_active_listen_day
    , DIV0NULL(SUM(play_cnt_own), COUNT_IF(is_active_listen)) AS avg_plays_own_per_active_listen_day
    , DIV0NULL(SUM(play_cnt_others), COUNT_IF(is_active_listen)) AS avg_plays_others_per_active_listen_day
    , SUM(play_duration_sec) AS total_play_duration_seconds
    , SUM(play_duration_sec_own) AS total_play_duration_seconds_own
    , SUM(play_duration_sec_others) AS total_play_duration_seconds_others
    , DIV0NULL(SUM(play_duration_sec_own), SUM(play_duration_sec)) AS pct_play_duration_own
    , DIV0NULL(SUM(play_duration_sec_others), SUM(play_duration_sec_own)) AS ratio_play_duration_others_to_own
    , DIV0NULL(SUM(play_duration_sec_own), SUM(play_cnt_own)) AS avg_play_duration_seconds_own
    , DIV0NULL(SUM(play_duration_sec_others), SUM(play_cnt_others)) AS avg_play_duration_seconds_others
    , MAX(days_since_user_joined) AS days_since_user_joined
FROM
    {{ ref('stg_aggregated__agg_user_daily_spine') }}
GROUP BY 1, 2
