Untitled

INSERT INTO mart.analitycs_time_yandex 
WITH cour AS
  (
  SELECT order_id,
          replace(trim(visitParamExtractRaw(visitParamExtractRaw(extra_data, 'performer_info'), 'courier_name')), '"', '') AS courier_name,
          CASE
              WHEN status ='ORDER_REFUND' THEN 1
              WHEN status = 'ORDER_DELIVERED' THEN 301
              WHEN status = 'ORDER_POSTED' THEN 2
              WHEN status = 'ORDER_RECEIVED' THEN 3
              WHEN status = 'COURIER_RECEIVED_ORDER' THEN 4
              WHEN status = 'COURIER_ARRIVED_DELIVERY_ADDRESS' THEN 5
              ELSE 0
          END AS status
   FROM ods.dc_order),
                                            max_city AS
  (SELECT max(id) AS max_id
   FROM ods.merchant_cities),
                                            cities AS
  (SELECT rowNumberInAllBlocks()+1+
     (SELECT max_id
      FROM max_city) AS id,
          dc.id AS dc_id,
          dc.name
   FROM ods.dc_city dc
   LEFT JOIN ods.merchant_cities mc ON mc.name = dc.name
   WHERE mc.name =''
   GROUP BY dc.name,
            dc_id),
                                            cits AS
  (SELECT *
   FROM cities
   UNION ALL SELECT mc.id,
                    case
          when dc.id = '' then NULL
          else dc.id
      end as dc_id,
                    mc.name
   FROM ods.merchant_cities mc
   LEFT JOIN ods.dc_city dc ON mc.name = dc.name),
                                            a AS
  (SELECT o.order_id,
          CASE
              WHEN trim
                     (REPLACE (visitParamExtractRaw(visitParamExtractRaw(le.extra_data, 'logistic_order'), 'status'), '"', '')) = 'estimating' THEN event_created_at
              ELSE NULL
          END AS estimating,
          CASE
              WHEN trim
                     (REPLACE (visitParamExtractRaw(visitParamExtractRaw(le.extra_data, 'logistic_order'), 'status'), '"', '')) = 'ready_for_approval' THEN event_created_at
              ELSE NULL
          END AS ready_for_approval,
          CASE
              WHEN trim
                     (REPLACE (visitParamExtractRaw(visitParamExtractRaw(le.extra_data, 'logistic_order'), 'status'), '"', '')) = 'performer_draft' THEN event_created_at
              ELSE NULL
          END AS courier_search_start_ts,
          CASE
              WHEN trim
                     (REPLACE (visitParamExtractRaw(visitParamExtractRaw(le.extra_data, 'logistic_order'), 'status'), '"', '')) = 'performer_found' THEN event_created_at
              ELSE NULL
          END AS courier_searched_at,
          CASE
              WHEN trim
                     (REPLACE (visitParamExtractRaw(visitParamExtractRaw(le.extra_data, 'logistic_order'), 'status'), '"', '')) = 'pickup_arrived' THEN event_created_at
              ELSE NULL
          END AS delivery_status_wait_for_courier_payment_ts,
          CASE
              WHEN trim
                     (REPLACE (visitParamExtractRaw(visitParamExtractRaw(le.extra_data, 'logistic_order'), 'status'), '"', '')) = 'pickuped' THEN event_created_at
              ELSE NULL
          END AS delivery_status_courier_paid_for_order_ts,
          CASE
              WHEN trim
                     (REPLACE (visitParamExtractRaw(visitParamExtractRaw(le.extra_data, 'logistic_order'), 'status'), '"', '')) = 'delivery_arrived' THEN event_created_at - interval 2 minute
              ELSE NULL
          END AS courier_delivered_at,
          CASE
              WHEN trim
                     (REPLACE (visitParamExtractRaw(visitParamExtractRaw(le.extra_data, 'logistic_order'), 'status'), '"', '')) = 'delivered' THEN event_created_at
              ELSE NULL
          END AS delivered
   FROM ods.dc_log_event le
   LEFT JOIN ods.dc_order o ON o.id = le.entity_id)
   ,
                                            performer_found AS
  (SELECT order_id,
          max(estimating) AS estimating,
          max(ready_for_approval) AS ready_for_approval,
          max(courier_search_start_ts) AS courier_search_start_ts,
          max(courier_searched_at) AS courier_searched_at,
          max(delivery_status_wait_for_courier_payment_ts) AS delivery_status_wait_for_courier_payment_ts,
          max(delivery_status_courier_paid_for_order_ts) AS delivery_status_courier_paid_for_order_ts,
          max(courier_delivered_at) AS delivery_arrived,
          max(delivered) AS delivered
   FROM a
   GROUP BY order_id)   
   ,
                                            tmp AS
  (SELECT do.id AS id,
          do.order_id AS order_id,
          CASE
              WHEN courier_searched_at IS NOT NULL
                   AND courier_search_start_ts IS NULL THEN 1
              ELSE 0
          END flag_1,
          CASE
              WHEN delivery_status_wait_for_courier_payment_ts IS NOT NULL
                   AND courier_searched_at IS NULL THEN 1
              ELSE 0
          END flag_2,
          CASE
              WHEN delivery_status_courier_paid_for_order_ts IS NOT NULL
                   AND courier_searched_at IS NULL THEN 1
              ELSE 0
          END flag_3,
          CASE
              WHEN delivery_status_wait_for_courier_payment_ts IS NOT NULL
                   AND delivery_status_courier_paid_for_order_ts IS NULL THEN 1
              ELSE 0
          END flag_4,
          CASE
              WHEN delivery_status_courier_paid_for_order_ts IS NOT NULL
                   AND delivery_status_wait_for_courier_payment_ts IS NULL THEN 1
              ELSE 0
          END flag_5,
          CASE
              WHEN delivered IS NOT NULL
                   AND delivery_arrived IS NULL THEN 1
              ELSE 0
          END flag_6,
          CASE
              WHEN courier_searched_at IS NOT NULL
                   AND courier_search_start_ts IS NULL THEN 1
              ELSE 0
          END flag_7,
          CASE
              WHEN courier_search_start_ts < do.created_at THEN do.created_at
              ELSE courier_search_start_ts
          END AS a_1,
          CASE
              WHEN delivery_status_courier_paid_for_order_ts IS NOT NULL THEN multiIf(isNotNull(do.delivered_at), do.delivered_at, cancelled_at)
              ELSE delivery_status_wait_for_courier_payment_ts
          END AS a_2,
          CASE
              WHEN delivery_status_courier_paid_for_order_ts IS NOT NULL THEN multiIf(isNotNull(do.delivered_at), do.delivered_at, cancelled_at)
              ELSE delivery_status_wait_for_courier_payment_ts
          END AS a_3,
          CASE
              WHEN delivery_arrived IS NOT NULL THEN multiIf(isNotNull(do.delivered_at), do.delivered_at, cancelled_at)
              ELSE delivery_status_courier_paid_for_order_ts
          END AS a_4,
          CASE
              WHEN delivered IS NOT NULL THEN multiIf(isNotNull(do.delivered_at), do.delivered_at, cancelled_at)
              ELSE delivery_status_courier_paid_for_order_ts
          END AS a_5,
          CASE
              WHEN courier_search_start_ts IS NOT NULL
                   AND courier_searched_at IS NOT NULL
                   AND courier_searched_at < courier_search_start_ts THEN courier_searched_at - do.created_at
              ELSE courier_search_start_ts - do.created_at
          END a_7,
          CASE
              WHEN delivery_status_wait_for_courier_payment_ts IS NOT NULL THEN multiIf(isNotNull(do.delivered_at), do.delivered_at, cancelled_at)
              ELSE courier_search_start_ts
          END AS b_1,
          courier_searched_at AS courier_searched_at,
          courier_searched_at AS courier_order_accepted_at,
          delivery_status_wait_for_courier_payment_ts AS courier_restaurant_arrived_at,
          delivery_status_courier_paid_for_order_ts AS courier_received_order_at,
          delivery_arrived AS courier_arrive_delivery_address_at,
          delivered_at AS courier_delivered_at,
          multiIf(isNotNull(do.delivered_at), do.delivered_at, cancelled_at) AS order_completion_date,
          'KFC' AS merchant_name,
          do.created_at AS created_at,
          courier_search_start_ts AS order_started_at,
          CASE
              WHEN (delivery_status_wait_for_courier_payment_ts-delivery_status_courier_paid_for_order_ts)/60<5
                   OR (delivery_status_wait_for_courier_payment_ts-delivery_status_courier_paid_for_order_ts)/60 IS NULL THEN 5
              WHEN (delivery_status_wait_for_courier_payment_ts-delivery_status_courier_paid_for_order_ts)/60<10 THEN 10
              ELSE 15
          END wait_time,
          CASE
              WHEN delivered_at > parseDateTimeBestEffortOrNull(replace(visitParamExtractRaw(extra_data, 'expected_time'), '"', '')) THEN 'Нет'
              WHEN delivered_at <= parseDateTimeBestEffortOrNull(replace(visitParamExtractRaw(extra_data, 'expected_time'), '"', '')) THEN 'Да'
              ELSE NULL
          END AS order_on_time,
          parseDateTimeBestEffortOrNull(replace(visitParamExtractRaw(extra_data, 'expected_time'), '"', '')) AS delivery_expected_time,
          status,
          trim(replace(visitParamExtractRaw(do.extra_data, 'place_id'), '"', '')) AS place_id
   FROM ods.dc_order do
   JOIN performer_found pf ON pf.order_id = do.order_id) 
SELECT
   merchant_name,
       mp.merchant_place_id AS merchant_place_id,
       'Яндекс доставка' AS delivery_service_name,
       mp.name AS place_name,
       case
           when cc.id is null then 0
           else cc.id
       end as city_id,
       tmp.created_at AS created_at,
       courier_order_accepted_at,
       order_completion_date,
       NULL AS group_id,
       tmp.id AS id,
       tmp.order_id,
       courier_name AS courier_id,
       cour.status AS status,
       wait_time,
       NULL AS count_transfers,
       CASE
           WHEN order_on_time = 'Да'
                AND courier_delivered_at <= delivery_expected_time THEN 1
           WHEN order_on_time = 'Да'
                AND (courier_delivered_at > delivery_expected_time) THEN 2
           WHEN order_on_time = 'Нет'
                AND tmp.status = 'ORDER_DELIVERED' THEN 3
           ELSE cour.status
       END AS on_time,
       multiIf(flag_1 = 1, (b_1 - a_1)/ 60, (courier_searched_at - a_1)/ 60) AS courier_searched_time,
       NULL AS courier_accepted_time,
       multiIf(flag_3 = 1, (courier_searched_at - a_3) / 60, (courier_restaurant_arrived_at - courier_order_accepted_at) / 60) AS courier_restaurant_arrived_time,
       multiIf(flag_4 = 1, (courier_restaurant_arrived_at - a_4) / 60, (courier_received_order_at - courier_restaurant_arrived_at) / 60) AS courier_wait_restaurant_time,
       multiIf(flag_5 = 1, (courier_received_order_at - a_5) / 60, (courier_arrive_delivery_address_at - courier_received_order_at) / 60) AS delivery_time,
       multiIf(flag_6 = 1, (courier_arrive_delivery_address_at - order_completion_date) / 60, (courier_delivered_at - courier_arrive_delivery_address_at) / 60) AS handing_time,
       multiIf(isNull(order_started_at)
               AND isNull(courier_searched_at)
               AND (tmp.status != 'ORDER_DELIVERED'),(order_completion_date-created_at) / 60, a_7 / 60) AS time_start_search_courier
FROM tmp
LEFT JOIN ods.dc_place mp ON mp.id = place_id
LEFT JOIN ods.dc_city c ON c.id = mp.city_id
LEFT JOIN cits cc ON cc.dc_id = c.id
LEFT JOIN cour ON cour.order_id = tmp.order_id