{{
    config(
        materialized = 'incremental'
        , incremental_strategy = 'delete+insert'
        , unique_key = ['clip_id']
        , cluster_by = ['p_date', 'p_hour']
    )
}}

SELECT
    cs.clip_id
    , cs.clip_creator_user_id
    , COUNT(DISTINCT cs.user_id) AS n_unique_users_shared
    , COUNT(DISTINCT CASE WHEN NOT cs.is_user_song_owner THEN cs.user_id END) AS n_unique_non_creator_users_shared
    , COUNT(*) AS n_shares
    , COUNT_IF(cs.is_user_song_owner) AS n_creator_shares
    , COUNT_IF(NOT cs.is_user_song_owner) AS n_non_creator_shares
    , COUNT(DISTINCT cs.share_platform) AS n_shared_platforms
    , MIN(cs.timestamp) AS first_share_timestamp
    , MAX(cs.timestamp) AS last_share_timestamp
    , dc.p_date
    , dc.p_hour
FROM {{ ref('stg_backend_events__clip_share') }} cs
LEFT JOIN {{ ref('dim_clip') }} dc
    ON cs.clip_id = dc.clip_id
WHERE 1=1
{% if is_incremental() %}
    -- Only recompute for clips that have been shared since the last run
    AND cs.clip_id IN (
        SELECT DISTINCT clip_id
        FROM {{ ref('stg_backend_events__clip_share') }}
        WHERE timestamp > (SELECT MAX(last_share_timestamp) FROM {{ this }})
    )
{% endif %}
GROUP BY
    cs.clip_id
    , cs.clip_creator_user_id
    , dc.p_date
    , dc.p_hour
