CREATE OR REPLACE TEMPORARY TABLE {{ dedup_table_name }} AS (
    SELECT * FROM (
        SELECT
            *
            , ROW_NUMBER() OVER (PARTITION BY
                song_session_id
                , studio_project_session_id
                , user_id
                , start_time
                , end_time
                , clip_length
                , song_id
                , play_duration
                , action_index
                , country
                , anonymous_id
                , is_user_song_owner
                , session_id
                , FLOOR(EXTRACT(epoch_millisecond FROM client_timestamp::TIMESTAMP_NTZ(9)) / 200) * 200 -- 200ms precision
                ORDER BY server_timestamp ASC
            ) AS rn
        FROM {{ studio_event_table_name }}
        WHERE 1=1
            AND IFF(
                '{{ buffered_partition_end_date }}' = '{{ buffered_partition_start_date }}',
                -- Single day case
                p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }} AND p_hour < {{ buffered_partition_end_hour }},
                -- Multi-day case
                (
                    (p_date = '{{ buffered_partition_start_date }}' AND p_hour >= {{ buffered_partition_start_hour }}) OR
                    (p_date > '{{ buffered_partition_start_date }}' AND p_date < '{{ buffered_partition_end_date }}') OR
                    (p_date = '{{ buffered_partition_end_date }}' AND p_hour < {{ buffered_partition_end_hour }})
                )
            )
            AND context_type = 'studio_new' -- 'studio_new' logs per-clip playback data. 'studio_new_timeline' logs per-timeline playback data, i.e. aggregated for all clips in the timeline.
            AND action_name IN (
                'PlayNewSong',
                'PlaySong',
                'PauseSong'
            )
            AND SPLIT(click_source_url, '/')[1] = 'studio' -- Filter for Studio sessions, as EditV3 also uses context_type = 'studio_new' under url '/edit/...'
    ) WHERE rn = 1
);

