{{
    config(
        materialized='incremental',
        incremental_strategy='merge',
        unique_key='hook_id'
    )
}}

WITH new_data AS (
    SELECT
        hook_id
        , COUNT(*) AS distinct_users_interacted

        -- Time bounds
        , MIN(first_interaction_hour) AS first_interaction_hour
        , MAX(last_interaction_hour) AS last_interaction_hour

        -- Aggregated playback metrics
        , SUM(hook_session_count) AS hook_session_count
        , SUM(hook_session_duration_seconds) AS hook_session_duration_seconds
        , SUM(play_duration_seconds) AS play_duration_seconds
        , SUM(unique_play_duration_seconds) AS unique_play_duration_seconds
        , SUM(long_watch_session_count) AS long_watch_session_count
        , SUM(repeat_count) AS repeat_count

        -- Aggregated like/unlike counts
        , SUM(like_count) AS like_count
        , SUM(undo_like_count) AS undo_like_count
        , SUM(net_like_count) AS net_like_count
        , SUM(dislike_count) AS dislike_count
        , SUM(undo_dislike_count) AS undo_dislike_count
        , SUM(net_dislike_count) AS net_dislike_count

        -- Aggregated comment counts
        , SUM(comment_open_tap_count) AS comment_open_tap_count
        , SUM(comment_begin_write_count) AS comment_begin_write_count
        , SUM(comment_count) AS comment_count

        -- Aggregated share counts
        , SUM(share_tap_count) AS share_tap_count
        , SUM(share_count) AS share_count

        -- Aggregated profile and follow/unfollow counts
        , SUM(profile_tap_count) AS profile_tap_count
        , SUM(follow_count) AS follow_count
        , SUM(unfollow_count) AS unfollow_count
        , SUM(net_follow_count) AS net_follow_count

        -- Aggregated playlist event counts
        , SUM(playlist_add_tap_count) AS playlist_add_tap_count
        , SUM(playlist_add_count) AS playlist_add_count

        -- Aggregated create hook counts
        , SUM(create_hook_tap_count) AS create_hook_tap_count
        , SUM(create_hook_submit_count) AS create_hook_submit_count
        , SUM(create_hook_success_count) AS create_hook_success_count
        , SUM(remix_tap_count) AS remix_tap_count

        -- Aggregated misc button taps & event fire counts
        , SUM(song_pill_tap_count) AS song_pill_tap_count
        , SUM(go_to_full_song_tap_count) AS go_to_full_song_tap_count
        , SUM(song_title_tap_count) AS song_title_tap_count

        -- Aggregated report/hide creator counts
        , SUM(report_count) AS report_count
        , SUM(hide_creator_count) AS hide_creator_count

        -- Recommended interactions totals
        , OBJECT_CONSTRUCT(
            'hook_session_count', SUM(recommended_interactions:hook_session_count::INTEGER),
            'hook_session_duration_seconds', SUM(recommended_interactions:hook_session_duration_seconds::FLOAT),
            'play_duration_seconds', SUM(recommended_interactions:play_duration_seconds::FLOAT),
            'unique_play_duration_seconds', SUM(recommended_interactions:unique_play_duration_seconds::FLOAT),
            'long_watch_session_count', SUM(recommended_interactions:long_watch_session_count::INTEGER),
            'repeat_count', SUM(recommended_interactions:repeat_count::INTEGER),
            'like_count', SUM(recommended_interactions:like_count::INTEGER),
            'undo_like_count', SUM(recommended_interactions:undo_like_count::INTEGER),
            'net_like_count', SUM(recommended_interactions:net_like_count::INTEGER),
            'dislike_count', SUM(recommended_interactions:dislike_count::INTEGER),
            'undo_dislike_count', SUM(recommended_interactions:undo_dislike_count::INTEGER),
            'net_dislike_count', SUM(recommended_interactions:net_dislike_count::INTEGER),
            'comment_open_tap_count', SUM(recommended_interactions:comment_open_tap_count::INTEGER),
            'comment_begin_write_count', SUM(recommended_interactions:comment_begin_write_count::INTEGER),
            'comment_count', SUM(recommended_interactions:comment_count::INTEGER),
            'share_tap_count', SUM(recommended_interactions:share_tap_count::INTEGER),
            'share_count', SUM(recommended_interactions:share_count::INTEGER),
            'profile_tap_count', SUM(recommended_interactions:profile_tap_count::INTEGER),
            'follow_count', SUM(recommended_interactions:follow_count::INTEGER),
            'unfollow_count', SUM(recommended_interactions:unfollow_count::INTEGER),
            'net_follow_count', SUM(recommended_interactions:net_follow_count::INTEGER),
            'playlist_add_tap_count', SUM(recommended_interactions:playlist_add_tap_count::INTEGER),
            'playlist_add_count', SUM(recommended_interactions:playlist_add_count::INTEGER),
            'create_hook_tap_count', SUM(recommended_interactions:create_hook_tap_count::INTEGER),
            'create_hook_submit_count', SUM(recommended_interactions:create_hook_submit_count::INTEGER),
            'create_hook_success_count', SUM(recommended_interactions:create_hook_success_count::INTEGER),
            'remix_tap_count', SUM(recommended_interactions:remix_tap_count::INTEGER),
            'song_pill_tap_count', SUM(recommended_interactions:song_pill_tap_count::INTEGER),
            'go_to_full_song_tap_count', SUM(recommended_interactions:go_to_full_song_tap_count::INTEGER),
            'song_title_tap_count', SUM(recommended_interactions:song_title_tap_count::INTEGER),
            'report_count', SUM(recommended_interactions:report_count::INTEGER),
            'hide_creator_count', SUM(recommended_interactions:hide_creator_count::INTEGER)
        ) AS recommended_interactions

        -- Organic interactions totals
        , OBJECT_CONSTRUCT(
            'hook_session_count', SUM(organic_interactions:hook_session_count::INTEGER),
            'hook_session_duration_seconds', SUM(organic_interactions:hook_session_duration_seconds::FLOAT),
            'play_duration_seconds', SUM(organic_interactions:play_duration_seconds::FLOAT),
            'unique_play_duration_seconds', SUM(organic_interactions:unique_play_duration_seconds::FLOAT),
            'long_watch_session_count', SUM(organic_interactions:long_watch_session_count::INTEGER),
            'repeat_count', SUM(organic_interactions:repeat_count::INTEGER),
            'like_count', SUM(organic_interactions:like_count::INTEGER),
            'undo_like_count', SUM(organic_interactions:undo_like_count::INTEGER),
            'net_like_count', SUM(organic_interactions:net_like_count::INTEGER),
            'dislike_count', SUM(organic_interactions:dislike_count::INTEGER),
            'undo_dislike_count', SUM(organic_interactions:undo_dislike_count::INTEGER),
            'net_dislike_count', SUM(organic_interactions:net_dislike_count::INTEGER),
            'comment_open_tap_count', SUM(organic_interactions:comment_open_tap_count::INTEGER),
            'comment_begin_write_count', SUM(organic_interactions:comment_begin_write_count::INTEGER),
            'comment_count', SUM(organic_interactions:comment_count::INTEGER),
            'share_tap_count', SUM(organic_interactions:share_tap_count::INTEGER),
            'share_count', SUM(organic_interactions:share_count::INTEGER),
            'profile_tap_count', SUM(organic_interactions:profile_tap_count::INTEGER),
            'follow_count', SUM(organic_interactions:follow_count::INTEGER),
            'unfollow_count', SUM(organic_interactions:unfollow_count::INTEGER),
            'net_follow_count', SUM(organic_interactions:net_follow_count::INTEGER),
            'playlist_add_tap_count', SUM(organic_interactions:playlist_add_tap_count::INTEGER),
            'playlist_add_count', SUM(organic_interactions:playlist_add_count::INTEGER),
            'create_hook_tap_count', SUM(organic_interactions:create_hook_tap_count::INTEGER),
            'create_hook_submit_count', SUM(organic_interactions:create_hook_submit_count::INTEGER),
            'create_hook_success_count', SUM(organic_interactions:create_hook_success_count::INTEGER),
            'remix_tap_count', SUM(organic_interactions:remix_tap_count::INTEGER),
            'song_pill_tap_count', SUM(organic_interactions:song_pill_tap_count::INTEGER),
            'go_to_full_song_tap_count', SUM(organic_interactions:go_to_full_song_tap_count::INTEGER),
            'song_title_tap_count', SUM(organic_interactions:song_title_tap_count::INTEGER),
            'report_count', SUM(organic_interactions:report_count::INTEGER),
            'hide_creator_count', SUM(organic_interactions:hide_creator_count::INTEGER)
        ) AS organic_interactions

    FROM {{ ref('int_user_hook_interactions_lifetime') }}
    WHERE 1=1
        {% if is_incremental() %}
            AND last_interaction_hour > (SELECT MAX(last_interaction_hour) FROM {{ this }})
        {% endif %}
    GROUP BY
        hook_id
)

-- If incremental, pull existing stats and aggregate together
{% if is_incremental() %}
, existing_data AS (
    SELECT * FROM {{ this }}
    WHERE hook_id IN (
        SELECT DISTINCT hook_id FROM new_data
    )
)

SELECT
    COALESCE(n.hook_id, e.hook_id) as hook_id,
    COALESCE(n.distinct_users_interacted, 0) + COALESCE(e.distinct_users_interacted, 0) as distinct_users_interacted,

    -- Time bounds
    LEAST(COALESCE(n.first_interaction_hour, e.first_interaction_hour), COALESCE(e.first_interaction_hour, n.first_interaction_hour)) as first_interaction_hour,
    GREATEST(COALESCE(n.last_interaction_hour, e.last_interaction_hour), COALESCE(e.last_interaction_hour, n.last_interaction_hour)) as last_interaction_hour,

    -- Aggregated playback metrics
    COALESCE(n.hook_session_count, 0) + COALESCE(e.hook_session_count, 0) as hook_session_count,
    COALESCE(n.hook_session_duration_seconds, 0) + COALESCE(e.hook_session_duration_seconds, 0) as hook_session_duration_seconds,
    COALESCE(n.play_duration_seconds, 0) + COALESCE(e.play_duration_seconds, 0) as play_duration_seconds,
    COALESCE(n.unique_play_duration_seconds, 0) + COALESCE(e.unique_play_duration_seconds, 0) as unique_play_duration_seconds,
    COALESCE(n.long_watch_session_count, 0) + COALESCE(e.long_watch_session_count, 0) as long_watch_session_count,
    COALESCE(n.repeat_count, 0) + COALESCE(e.repeat_count, 0) as repeat_count,

    -- Aggregated like/unlike counts
    COALESCE(n.like_count, 0) + COALESCE(e.like_count, 0) as like_count,
    COALESCE(n.undo_like_count, 0) + COALESCE(e.undo_like_count, 0) as undo_like_count,
    COALESCE(n.net_like_count, 0) + COALESCE(e.net_like_count, 0) as net_like_count,
    COALESCE(n.dislike_count, 0) + COALESCE(e.dislike_count, 0) as dislike_count,
    COALESCE(n.undo_dislike_count, 0) + COALESCE(e.undo_dislike_count, 0) as undo_dislike_count,
    COALESCE(n.net_dislike_count, 0) + COALESCE(e.net_dislike_count, 0) as net_dislike_count,

    -- Aggregated comment counts
    COALESCE(n.comment_open_tap_count, 0) + COALESCE(e.comment_open_tap_count, 0) as comment_open_tap_count,
    COALESCE(n.comment_begin_write_count, 0) + COALESCE(e.comment_begin_write_count, 0) as comment_begin_write_count,
    COALESCE(n.comment_count, 0) + COALESCE(e.comment_count, 0) as comment_count,

    -- Aggregated share counts
    COALESCE(n.share_tap_count, 0) + COALESCE(e.share_tap_count, 0) as share_tap_count,
    COALESCE(n.share_count, 0) + COALESCE(e.share_count, 0) as share_count,

    -- Aggregated profile and follow/unfollow counts
    COALESCE(n.profile_tap_count, 0) + COALESCE(e.profile_tap_count, 0) as profile_tap_count,
    COALESCE(n.follow_count, 0) + COALESCE(e.follow_count, 0) as follow_count,
    COALESCE(n.unfollow_count, 0) + COALESCE(e.unfollow_count, 0) as unfollow_count,
    COALESCE(n.net_follow_count, 0) + COALESCE(e.net_follow_count, 0) as net_follow_count,

    -- Aggregated playlist event counts
    COALESCE(n.playlist_add_tap_count, 0) + COALESCE(e.playlist_add_tap_count, 0) as playlist_add_tap_count,
    COALESCE(n.playlist_add_count, 0) + COALESCE(e.playlist_add_count, 0) as playlist_add_count,

    -- Aggregated create hook counts
    COALESCE(n.create_hook_tap_count, 0) + COALESCE(e.create_hook_tap_count, 0) as create_hook_tap_count,
    COALESCE(n.create_hook_submit_count, 0) + COALESCE(e.create_hook_submit_count, 0) as create_hook_submit_count,
    COALESCE(n.create_hook_success_count, 0) + COALESCE(e.create_hook_success_count, 0) as create_hook_success_count,
    COALESCE(n.remix_tap_count, 0) + COALESCE(e.remix_tap_count, 0) as remix_tap_count,

    -- Aggregated misc button taps & event fire counts
    COALESCE(n.song_pill_tap_count, 0) + COALESCE(e.song_pill_tap_count, 0) as song_pill_tap_count,
    COALESCE(n.go_to_full_song_tap_count, 0) + COALESCE(e.go_to_full_song_tap_count, 0) as go_to_full_song_tap_count,
    COALESCE(n.song_title_tap_count, 0) + COALESCE(e.song_title_tap_count, 0) as song_title_tap_count,

    -- Aggregated report/hide creator counts
    COALESCE(n.report_count, 0) + COALESCE(e.report_count, 0) as report_count,
    COALESCE(n.hide_creator_count, 0) + COALESCE(e.hide_creator_count, 0) as hide_creator_count,

    -- Recommended interactions totals (combining JSON objects)
    OBJECT_CONSTRUCT(
        'hook_session_count', COALESCE(n.recommended_interactions:hook_session_count::INTEGER, 0) + COALESCE(e.recommended_interactions:hook_session_count::INTEGER, 0),
        'hook_session_duration_seconds', COALESCE(n.recommended_interactions:hook_session_duration_seconds::FLOAT, 0) + COALESCE(e.recommended_interactions:hook_session_duration_seconds::FLOAT, 0),
        'play_duration_seconds', COALESCE(n.recommended_interactions:play_duration_seconds::FLOAT, 0) + COALESCE(e.recommended_interactions:play_duration_seconds::FLOAT, 0),
        'unique_play_duration_seconds', COALESCE(n.recommended_interactions:unique_play_duration_seconds::FLOAT, 0) + COALESCE(e.recommended_interactions:unique_play_duration_seconds::FLOAT, 0),
        'long_watch_session_count', COALESCE(n.recommended_interactions:long_watch_session_count::INTEGER, 0) + COALESCE(e.recommended_interactions:long_watch_session_count::INTEGER, 0),
        'repeat_count', COALESCE(n.recommended_interactions:repeat_count::INTEGER, 0) + COALESCE(e.recommended_interactions:repeat_count::INTEGER, 0),
        'like_count', COALESCE(n.recommended_interactions:like_count::INTEGER, 0) + COALESCE(e.recommended_interactions:like_count::INTEGER, 0),
        'undo_like_count', COALESCE(n.recommended_interactions:undo_like_count::INTEGER, 0) + COALESCE(e.recommended_interactions:undo_like_count::INTEGER, 0),
        'net_like_count', COALESCE(n.recommended_interactions:net_like_count::INTEGER, 0) + COALESCE(e.recommended_interactions:net_like_count::INTEGER, 0),
        'dislike_count', COALESCE(n.recommended_interactions:dislike_count::INTEGER, 0) + COALESCE(e.recommended_interactions:dislike_count::INTEGER, 0),
        'undo_dislike_count', COALESCE(n.recommended_interactions:undo_dislike_count::INTEGER, 0) + COALESCE(e.recommended_interactions:undo_dislike_count::INTEGER, 0),
        'net_dislike_count', COALESCE(n.recommended_interactions:net_dislike_count::INTEGER, 0) + COALESCE(e.recommended_interactions:net_dislike_count::INTEGER, 0),
        'comment_open_tap_count', COALESCE(n.recommended_interactions:comment_open_tap_count::INTEGER, 0) + COALESCE(e.recommended_interactions:comment_open_tap_count::INTEGER, 0),
        'comment_begin_write_count', COALESCE(n.recommended_interactions:comment_begin_write_count::INTEGER, 0) + COALESCE(e.recommended_interactions:comment_begin_write_count::INTEGER, 0),
        'comment_count', COALESCE(n.recommended_interactions:comment_count::INTEGER, 0) + COALESCE(e.recommended_interactions:comment_count::INTEGER, 0),
        'share_tap_count', COALESCE(n.recommended_interactions:share_tap_count::INTEGER, 0) + COALESCE(e.recommended_interactions:share_tap_count::INTEGER, 0),
        'share_count', COALESCE(n.recommended_interactions:share_count::INTEGER, 0) + COALESCE(e.recommended_interactions:share_count::INTEGER, 0),
        'profile_tap_count', COALESCE(n.recommended_interactions:profile_tap_count::INTEGER, 0) + COALESCE(e.recommended_interactions:profile_tap_count::INTEGER, 0),
        'follow_count', COALESCE(n.recommended_interactions:follow_count::INTEGER, 0) + COALESCE(e.recommended_interactions:follow_count::INTEGER, 0),
        'unfollow_count', COALESCE(n.recommended_interactions:unfollow_count::INTEGER, 0) + COALESCE(e.recommended_interactions:unfollow_count::INTEGER, 0),
        'net_follow_count', COALESCE(n.recommended_interactions:net_follow_count::INTEGER, 0) + COALESCE(e.recommended_interactions:net_follow_count::INTEGER, 0),
        'playlist_add_tap_count', COALESCE(n.recommended_interactions:playlist_add_tap_count::INTEGER, 0) + COALESCE(e.recommended_interactions:playlist_add_tap_count::INTEGER, 0),
        'playlist_add_count', COALESCE(n.recommended_interactions:playlist_add_count::INTEGER, 0) + COALESCE(e.recommended_interactions:playlist_add_count::INTEGER, 0),
        'create_hook_tap_count', COALESCE(n.recommended_interactions:create_hook_tap_count::INTEGER, 0) + COALESCE(e.recommended_interactions:create_hook_tap_count::INTEGER, 0),
        'create_hook_submit_count', COALESCE(n.recommended_interactions:create_hook_submit_count::INTEGER, 0) + COALESCE(e.recommended_interactions:create_hook_submit_count::INTEGER, 0),
        'create_hook_success_count', COALESCE(n.recommended_interactions:create_hook_success_count::INTEGER, 0) + COALESCE(e.recommended_interactions:create_hook_success_count::INTEGER, 0),
        'remix_tap_count', COALESCE(n.recommended_interactions:remix_tap_count::INTEGER, 0) + COALESCE(e.recommended_interactions:remix_tap_count::INTEGER, 0),
        'song_pill_tap_count', COALESCE(n.recommended_interactions:song_pill_tap_count::INTEGER, 0) + COALESCE(e.recommended_interactions:song_pill_tap_count::INTEGER, 0),
        'go_to_full_song_tap_count', COALESCE(n.recommended_interactions:go_to_full_song_tap_count::INTEGER, 0) + COALESCE(e.recommended_interactions:go_to_full_song_tap_count::INTEGER, 0),
        'song_title_tap_count', COALESCE(n.recommended_interactions:song_title_tap_count::INTEGER, 0) + COALESCE(e.recommended_interactions:song_title_tap_count::INTEGER, 0),
        'report_count', COALESCE(n.recommended_interactions:report_count::INTEGER, 0) + COALESCE(e.recommended_interactions:report_count::INTEGER, 0),
        'hide_creator_count', COALESCE(n.recommended_interactions:hide_creator_count::INTEGER, 0) + COALESCE(e.recommended_interactions:hide_creator_count::INTEGER, 0)
    ) AS recommended_interactions,

    -- Organic interactions totals (combining JSON objects)
    OBJECT_CONSTRUCT(
        'hook_session_count', COALESCE(n.organic_interactions:hook_session_count::INTEGER, 0) + COALESCE(e.organic_interactions:hook_session_count::INTEGER, 0),
        'hook_session_duration_seconds', COALESCE(n.organic_interactions:hook_session_duration_seconds::FLOAT, 0) + COALESCE(e.organic_interactions:hook_session_duration_seconds::FLOAT, 0),
        'play_duration_seconds', COALESCE(n.organic_interactions:play_duration_seconds::FLOAT, 0) + COALESCE(e.organic_interactions:play_duration_seconds::FLOAT, 0),
        'unique_play_duration_seconds', COALESCE(n.organic_interactions:unique_play_duration_seconds::FLOAT, 0) + COALESCE(e.organic_interactions:unique_play_duration_seconds::FLOAT, 0),
        'long_watch_session_count', COALESCE(n.organic_interactions:long_watch_session_count::INTEGER, 0) + COALESCE(e.organic_interactions:long_watch_session_count::INTEGER, 0),
        'repeat_count', COALESCE(n.organic_interactions:repeat_count::INTEGER, 0) + COALESCE(e.organic_interactions:repeat_count::INTEGER, 0),
        'like_count', COALESCE(n.organic_interactions:like_count::INTEGER, 0) + COALESCE(e.organic_interactions:like_count::INTEGER, 0),
        'undo_like_count', COALESCE(n.organic_interactions:undo_like_count::INTEGER, 0) + COALESCE(e.organic_interactions:undo_like_count::INTEGER, 0),
        'net_like_count', COALESCE(n.organic_interactions:net_like_count::INTEGER, 0) + COALESCE(e.organic_interactions:net_like_count::INTEGER, 0),
        'dislike_count', COALESCE(n.organic_interactions:dislike_count::INTEGER, 0) + COALESCE(e.organic_interactions:dislike_count::INTEGER, 0),
        'undo_dislike_count', COALESCE(n.organic_interactions:undo_dislike_count::INTEGER, 0) + COALESCE(e.organic_interactions:undo_dislike_count::INTEGER, 0),
        'net_dislike_count', COALESCE(n.organic_interactions:net_dislike_count::INTEGER, 0) + COALESCE(e.organic_interactions:net_dislike_count::INTEGER, 0),
        'comment_open_tap_count', COALESCE(n.organic_interactions:comment_open_tap_count::INTEGER, 0) + COALESCE(e.organic_interactions:comment_open_tap_count::INTEGER, 0),
        'comment_begin_write_count', COALESCE(n.organic_interactions:comment_begin_write_count::INTEGER, 0) + COALESCE(e.organic_interactions:comment_begin_write_count::INTEGER, 0),
        'comment_count', COALESCE(n.organic_interactions:comment_count::INTEGER, 0) + COALESCE(e.organic_interactions:comment_count::INTEGER, 0),
        'share_tap_count', COALESCE(n.organic_interactions:share_tap_count::INTEGER, 0) + COALESCE(e.organic_interactions:share_tap_count::INTEGER, 0),
        'share_count', COALESCE(n.organic_interactions:share_count::INTEGER, 0) + COALESCE(e.organic_interactions:share_count::INTEGER, 0),
        'profile_tap_count', COALESCE(n.organic_interactions:profile_tap_count::INTEGER, 0) + COALESCE(e.organic_interactions:profile_tap_count::INTEGER, 0),
        'follow_count', COALESCE(n.organic_interactions:follow_count::INTEGER, 0) + COALESCE(e.organic_interactions:follow_count::INTEGER, 0),
        'unfollow_count', COALESCE(n.organic_interactions:unfollow_count::INTEGER, 0) + COALESCE(e.organic_interactions:unfollow_count::INTEGER, 0),
        'net_follow_count', COALESCE(n.organic_interactions:net_follow_count::INTEGER, 0) + COALESCE(e.organic_interactions:net_follow_count::INTEGER, 0),
        'playlist_add_tap_count', COALESCE(n.organic_interactions:playlist_add_tap_count::INTEGER, 0) + COALESCE(e.organic_interactions:playlist_add_tap_count::INTEGER, 0),
        'playlist_add_count', COALESCE(n.organic_interactions:playlist_add_count::INTEGER, 0) + COALESCE(e.organic_interactions:playlist_add_count::INTEGER, 0),
        'create_hook_tap_count', COALESCE(n.organic_interactions:create_hook_tap_count::INTEGER, 0) + COALESCE(e.organic_interactions:create_hook_tap_count::INTEGER, 0),
        'create_hook_submit_count', COALESCE(n.organic_interactions:create_hook_submit_count::INTEGER, 0) + COALESCE(e.organic_interactions:create_hook_submit_count::INTEGER, 0),
        'create_hook_success_count', COALESCE(n.organic_interactions:create_hook_success_count::INTEGER, 0) + COALESCE(e.organic_interactions:create_hook_success_count::INTEGER, 0),
        'remix_tap_count', COALESCE(n.organic_interactions:remix_tap_count::INTEGER, 0) + COALESCE(e.organic_interactions:remix_tap_count::INTEGER, 0),
        'song_pill_tap_count', COALESCE(n.organic_interactions:song_pill_tap_count::INTEGER, 0) + COALESCE(e.organic_interactions:song_pill_tap_count::INTEGER, 0),
        'go_to_full_song_tap_count', COALESCE(n.organic_interactions:go_to_full_song_tap_count::INTEGER, 0) + COALESCE(e.organic_interactions:go_to_full_song_tap_count::INTEGER, 0),
        'song_title_tap_count', COALESCE(n.organic_interactions:song_title_tap_count::INTEGER, 0) + COALESCE(e.organic_interactions:song_title_tap_count::INTEGER, 0),
        'report_count', COALESCE(n.organic_interactions:report_count::INTEGER, 0) + COALESCE(e.organic_interactions:report_count::INTEGER, 0),
        'hide_creator_count', COALESCE(n.organic_interactions:hide_creator_count::INTEGER, 0) + COALESCE(e.organic_interactions:hide_creator_count::INTEGER, 0)
    ) AS organic_interactions

FROM new_data n
FULL OUTER JOIN existing_data e ON n.hook_id = e.hook_id

-- If not incremental, just use new data
{% else %}
SELECT
    hook_id,
    distinct_users_interacted,
    first_interaction_hour,
    last_interaction_hour,
    hook_session_count,
    hook_session_duration_seconds,
    play_duration_seconds,
    unique_play_duration_seconds,
    long_watch_session_count,
    repeat_count,
    like_count,
    undo_like_count,
    net_like_count,
    dislike_count,
    undo_dislike_count,
    net_dislike_count,
    comment_open_tap_count,
    comment_begin_write_count,
    comment_count,
    share_tap_count,
    share_count,
    profile_tap_count,
    follow_count,
    unfollow_count,
    net_follow_count,
    playlist_add_tap_count,
    playlist_add_count,
    create_hook_tap_count,
    create_hook_submit_count,
    create_hook_success_count,
    remix_tap_count,
    song_pill_tap_count,
    go_to_full_song_tap_count,
    song_title_tap_count,
    report_count,
    hide_creator_count,
    recommended_interactions,
    organic_interactions
FROM new_data
{% endif %}
