{{
    config(
        materialized = 'incremental'
        , incremental_strategy = 'delete+insert'
        , unique_key = ['user_id']
    )
}}

SELECT
    cs.user_id
    , COUNT(DISTINCT cs.clip_id) AS n_unique_clips_shared
    , COUNT(DISTINCT CASE WHEN cs.is_user_song_owner THEN cs.clip_id END) AS n_unique_own_clips_shared
    , COUNT(DISTINCT CASE WHEN NOT cs.is_user_song_owner THEN cs.clip_id END) AS n_unique_others_clips_shared
    , COUNT(*) AS n_shares
    , COUNT(DISTINCT cs.share_platform) AS n_shared_platforms
    , MIN(cs.timestamp) AS first_share_timestamp
    , MAX(cs.timestamp) AS last_share_timestamp
FROM {{ ref('stg_backend_events__clip_share') }} cs
WHERE 1=1
{% if is_incremental() %}
    AND cs.user_id IN (
        SELECT DISTINCT user_id
        FROM {{ ref('stg_backend_events__clip_share') }}
        WHERE timestamp > (SELECT MAX(last_share_timestamp) FROM {{ this }})
    )
{% endif %}
GROUP BY
    cs.user_id
