CREATE OR REPLACE TEMPORARY TABLE {{ temp_studio_project_clip_sessions_table_name }} AS (
    SELECT
        -- Primary key and identifiers
        sed.user_id AS user_uid
        , sed.song_id AS clip_id
        , sed.studio_project_session_id

        -- Playback
        , MIN(sed.client_timestamp::TIMESTAMP_NTZ(9)) AS play_start_ts_utc
        , MAX(sed.client_timestamp::TIMESTAMP_NTZ(9)) AS play_end_ts_utc
        , LEAST(DATEDIFF('second', MIN(sed.client_timestamp::TIMESTAMP_NTZ(9)), MAX(sed.client_timestamp::TIMESTAMP_NTZ(9))), 3 * 60 * 60) AS studio_project_session_duration_seconds
        , SUM(GREATEST(0, sed.play_duration)) AS play_duration_seconds -- Exclude negative play durations
        , MIN_BY(sed.action_name, sed.client_timestamp::TIMESTAMP_NTZ(9)) AS play_start_reason -- Currently just PlayNewSong and PlaySong
        , MAX_BY(sed.action_name, sed.client_timestamp::TIMESTAMP_NTZ(9)) AS play_end_reason -- Currently just PauseSong

        -- Clip info
        , ANY_VALUE(sed.song_length) AS clip_duration_seconds
        , ANY_VALUE(sed.is_user_song_owner) AS is_user_song_owner

        -- Context info
        , SPLIT(MIN_BY(sed.click_source_url, sed.client_timestamp::TIMESTAMP_NTZ(9)), '/')[1] AS play_page_type
        , NULL AS play_track_index
        , ARRAY_SLICE(ARRAY_AGG(
            OBJECT_CONSTRUCT(
                'action_name', sed.action_name,
                'client_timestamp', sed.client_timestamp::TIMESTAMP_NTZ(9),
                'server_timestamp', sed.server_timestamp::TIMESTAMP_NTZ(9),
                'start_time', sed.start_time,
                'end_time', sed.end_time,
                'play_duration_seconds', sed.play_duration
            )
        ) WITHIN GROUP (ORDER BY sed.client_timestamp::TIMESTAMP_NTZ(9)), 0, 200) AS audio_player_events
        , OBJECT_CONSTRUCT(
            'volume', MIN_BY(sed.volume, sed.client_timestamp::TIMESTAMP_NTZ(9))
        ) AS audio_player_metadata

        -- Device metadata
        , ANY_VALUE(sed.session_id) AS session_id
        , 'web' AS platform
        , ANY_VALUE(sed.is_mobile) AS is_mobile
        , MIN_BY(sed.country, sed.client_timestamp::TIMESTAMP_NTZ(9)) AS country
        , MIN_BY(sed.city, sed.client_timestamp::TIMESTAMP_NTZ(9)) AS city

        -- Partition columns
        , DATE(MIN_BY(sed.server_timestamp::TIMESTAMP_NTZ(9), sed.client_timestamp::TIMESTAMP_NTZ(9))) AS p_date
        , HOUR(MIN_BY(sed.server_timestamp::TIMESTAMP_NTZ(9), sed.client_timestamp::TIMESTAMP_NTZ(9))) AS p_hour
    FROM
        {{ dedup_table_name }} sed
    WHERE 1=1
        AND IFF(
            '{{ buffered_partition_start_date }}' = '{{ buffered_partition_end_date }}',
            sed.p_date = '{{ buffered_partition_start_date }}' AND sed.p_hour >= {{ buffered_partition_start_hour }} AND sed.p_hour < {{ buffered_partition_end_hour }},
            (
                (sed.p_date = '{{ buffered_partition_start_date }}' AND sed.p_hour >= {{ buffered_partition_start_hour }}) OR
                (sed.p_date > '{{ buffered_partition_start_date }}' AND sed.p_date < '{{ buffered_partition_end_date }}') OR
                (sed.p_date = '{{ buffered_partition_end_date }}' AND sed.p_hour < {{ buffered_partition_end_hour}})
            )
        )
    GROUP BY
        sed.user_id
        , sed.song_id
        , sed.studio_project_session_id
);

