K87L8BZBS6

Untitled

Oct 16th, 2024 (edited)
84
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Recreate table
  2. CREATE TABLE mart.naumen_calls_tmp (
  3.   like mart.naumen_calls including all,
  4.   wrapup_time BIGINT,
  5.   is_removed BOOLEAN,
  6.   removal_date TIMESTAMP
  7. );
  8.  
  9. CREATE LOCAL TEMP TABLE mart_naumen_calls_new_columns ON COMMIT PRESERVE ROWS AS
  10. WITH raw_data AS
  11. (
  12.     SELECT
  13.         COALESCE(naumen_calls.session_id, naumen_calls.uuid) AS join_key,
  14.         naumen_calls.wrapup_time,
  15.         naumen_calls.is_removed,
  16.         naumen_calls.removal_date,
  17.         ROW_NUMBER() OVER (
  18.             PARTITION BY session_id_with_case_uuid
  19.             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
  20.     FROM core.naumen_calls
  21.     LEFT JOIN core.amocrm_events_time_v4
  22.         ON naumen_calls.amo_lead_id = core.amocrm_events_time_v4.amo_lead_id
  23.     LEFT JOIN core.users_amo_lead_with_phone
  24.         ON naumen_calls.phone_numbers = users_amo_lead_with_phone.phone
  25. )
  26. SELECT
  27.     join_key,
  28.     wrapup_time,
  29.     is_removed,
  30.     removal_date
  31. FROM raw_data
  32. WHERE rn = 1;
  33.  
  34. CREATE LOCAL TEMP TABLE mart_naumen_joined ON COMMIT PRESERVE ROWS AS
  35. SELECT
  36.     naumen_calls.*,
  37.     mart_naumen_calls_new_columns.wrapup_time,
  38.     mart_naumen_calls_new_columns.is_removed,
  39.     mart_naumen_calls_new_columns.removal_date
  40. FROM mart.naumen_calls
  41. LEFT JOIN mart_naumen_calls_new_columns
  42.     ON COALESCE(naumen_calls.session_id, naumen_calls.uuid) = mart_naumen_calls_new_columns.join_key
  43.  
  44.  
  45.  
  46. -- Insert the results of the query into the new table
  47. INSERT INTO mart.naumen_calls_tmp (
  48.     session_id,
  49.     uuid,
  50.     project_uuid,
  51.     speaking_time,
  52.     attempt_start,
  53.     attempt_end,
  54.     attempt_result,
  55.     email,
  56.     phone_numbers,
  57.     project_title,
  58.     event_id,
  59.     amo_lead_id,
  60.     pupil_id,
  61.     is_last_call,
  62.     naumen_uuid_link,
  63.     is_first_call,
  64.     planned_phone_time,
  65.     creation_date,
  66.     uploaded_date,
  67.     modified_date,
  68.     tag,
  69.     composite_key,
  70.     wrapup_time_sec,
  71.     is_removed,
  72.     removal_date
  73. )
  74. SELECT *
  75. FROM mart_naumen_joined;
  76.  
  77. -- Transfer ownership of the new table
  78. ALTER TABLE IF EXISTS mart.naumen_calls_tmp OWNER TO "root-dwh";
  79.  
  80. -- Swap tables
  81. ALTER TABLE IF EXISTS mart.naumen_calls RENAME TO naumen_calls_old;
  82. ALTER TABLE IF EXISTS mart.naumen_calls_tmp RENAME TO naumen_calls;
  83.  
  84. -- Delete old table
  85. DROP TABLE IF EXISTS mart.naumen_calls_old;
Advertisement
Add Comment
Please, Sign In to add comment