Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Recreate table
- CREATE TABLE mart.naumen_calls_tmp (
- like mart.naumen_calls including all,
- wrapup_time BIGINT,
- is_removed BOOLEAN,
- removal_date TIMESTAMP
- );
- CREATE LOCAL TEMP TABLE mart_naumen_calls_new_columns ON COMMIT PRESERVE ROWS AS
- WITH raw_data AS
- (
- SELECT
- COALESCE(naumen_calls.session_id, naumen_calls.uuid) AS join_key,
- naumen_calls.wrapup_time,
- naumen_calls.is_removed,
- naumen_calls.removal_date,
- ROW_NUMBER() OVER (
- PARTITION BY session_id_with_case_uuid
- ORDER BY ABS(amocrm_events_time_v4.unix_created_at - EXTRACT(epoch FROM session_attempt_start_with_case_creation_date)), users_amo_lead_with_phone.is_unique DESC) AS rn
- FROM core.naumen_calls
- LEFT JOIN core.amocrm_events_time_v4
- ON naumen_calls.amo_lead_id = core.amocrm_events_time_v4.amo_lead_id
- LEFT JOIN core.users_amo_lead_with_phone
- ON naumen_calls.phone_numbers = users_amo_lead_with_phone.phone
- )
- SELECT
- join_key,
- wrapup_time,
- is_removed,
- removal_date
- FROM raw_data
- WHERE rn = 1;
- CREATE LOCAL TEMP TABLE mart_naumen_joined ON COMMIT PRESERVE ROWS AS
- SELECT
- naumen_calls.*,
- mart_naumen_calls_new_columns.wrapup_time,
- mart_naumen_calls_new_columns.is_removed,
- mart_naumen_calls_new_columns.removal_date
- FROM mart.naumen_calls
- LEFT JOIN mart_naumen_calls_new_columns
- ON COALESCE(naumen_calls.session_id, naumen_calls.uuid) = mart_naumen_calls_new_columns.join_key
- -- Insert the results of the query into the new table
- INSERT INTO mart.naumen_calls_tmp (
- session_id,
- uuid,
- project_uuid,
- speaking_time,
- attempt_start,
- attempt_end,
- attempt_result,
- email,
- phone_numbers,
- project_title,
- event_id,
- amo_lead_id,
- pupil_id,
- is_last_call,
- naumen_uuid_link,
- is_first_call,
- planned_phone_time,
- creation_date,
- uploaded_date,
- modified_date,
- tag,
- composite_key,
- wrapup_time_sec,
- is_removed,
- removal_date
- )
- SELECT *
- FROM mart_naumen_joined;
- -- Transfer ownership of the new table
- ALTER TABLE IF EXISTS mart.naumen_calls_tmp OWNER TO "root-dwh";
- -- Swap tables
- ALTER TABLE IF EXISTS mart.naumen_calls RENAME TO naumen_calls_old;
- ALTER TABLE IF EXISTS mart.naumen_calls_tmp RENAME TO naumen_calls;
- -- Delete old table
- DROP TABLE IF EXISTS mart.naumen_calls_old;
Advertisement
Add Comment
Please, Sign In to add comment