ivr_detail.txt

--Tabla
CREATE OR REPLACE TABLE keepcoding.ivr_detail AS

--calls
SELECT calls.ivr_id
     , calls.phone_number
     , calls.ivr_result
     , calls.vdn_label
     , calls.start_date
     , FORMAT_DATE('%Y%m%d', DATE(calls.start_date)) AS start_date_id
     , calls.end_date
     , FORMAT_DATE('%Y%m%d', DATE(calls.end_date)) AS end_date_id
     , calls.total_duration
     , calls.customer_segment
     , calls.ivr_language
     , calls.steps_module
     , calls.module_aggregation
--- Modules
     , IFNULL(modules.module_name, 'UNKNOWN') AS module_name
     , IFNULL(modules.module_duration, -9999999) AS module_duration
     , IFNULL(modules.module_result, 'UNKNOWN') AS module_result

-- Steps 
     , IFNULL(steps.module_sequece,-9999999) AS module_sequece
     , IFNULL(steps.step_sequence,-9999999) AS step_sequence
     , IFNULL(steps.step_name, 'UNKNOWN') AS step_name
     , IFNULL(steps.step_result, 'UNKNOWN') AS step_result
     , IFNULL(steps.step_description_error, 'UNKNOWN') AS step_description_error
     , IFNULL(steps.document_type, 'UNKNOWN') AS document_type
     , IFNULL(steps.document_identification, 'UNKNOWN') AS document_identification
     , IFNULL(steps.customer_phone, 'UNKNOWN') AS customer_phone
     , IFNULL(steps.billing_account_id, 'UNKNOWN') AS billing_account_id

--Juntar tablas
FROM `keepcoding.ivr_calls` calls
INNER 
JOIN `keepcoding.ivr_modules` modules 
  ON calls.ivr_id = modules.ivr_id
INNER 
JOIN `keepcoding.ivr_steps` steps
  ON modules.ivr_id = steps.ivr_id 
 AND modules.module_sequece = steps.module_sequece