Untitled

INSERT INTO mart.analitycs_time_md
WITH r AS
  (SELECT id,
          order_id,
          multiIf(isNotNull(courier_searched_at)
                  AND isNull(order_started_at), 1, 0) AS flag_1,
          multiIf(isNotNull(courier_order_accepted_at)
                  AND isNull(courier_searched_at), 1, 0) AS flag_2,
          multiIf(isNotNull(courier_restaurant_arrived_at)
                  AND isNull(courier_searched_at), 1, 0) AS flag_3,
          multiIf(isNotNull(courier_received_order_at)
                  AND isNull(courier_restaurant_arrived_at), 1, 0) AS flag_4,
          multiIf(isNotNull(courier_restaurant_arrived_at)
                  AND isNull(courier_received_order_at), 1, 0) AS flag_5,
          multiIf(isNotNull(courier_delivered_at)
                  AND isNull(courier_arrive_delivery_address_at), 1, 0) AS flag_6,
          multiIf(isNotNull(courier_searched_at)
                  AND isNull(order_started_at), 1, 0) AS flag_7,
          multiIf(order_started_at < created_at, created_at, order_started_at) AS a_1,
          multiIf(isNotNull(courier_restaurant_arrived_at), order_completion_date, courier_order_accepted_at) AS a_2,
          multiIf(isNotNull(courier_received_order_at), order_completion_date, courier_order_accepted_at) AS a_3,
          multiIf(isNotNull(courier_arrive_delivery_address_at), order_completion_date, courier_restaurant_arrived_at) AS a_4,
          multiIf(isNotNull(courier_delivered_at), order_completion_date, courier_received_order_at) AS a_5,
          multiIf(isNotNull(order_started_at)
                  AND isNotNull(courier_searched_at)
                  AND (courier_searched_at < order_started_at), courier_searched_at - created_at, order_started_at - created_at) AS a_7,
          multiIf(isNotNull(courier_order_accepted_at), order_started_at, order_completion_date) AS b_1,
          courier_searched_at,
          courier_order_accepted_at,
          courier_restaurant_arrived_at,
          courier_received_order_at,
          courier_arrive_delivery_address_at,
          courier_delivered_at,
          order_completion_date,
          merchant_name,
          merchant_place_id,
          delivery_service_name,
          place_name,
          delivery_city_id,
          created_at,
          order_started_at,
          group_id,
          status,
          multiIf((((courier_received_order_at - courier_restaurant_arrived_at) / 60) < 5)
                  OR isNull((courier_received_order_at - courier_restaurant_arrived_at) / 60), 5, ((courier_received_order_at - courier_restaurant_arrived_at) / 60) < 10, 10, 15) AS wait_time,
          floor((suggested_time - delivery_expected_time) / 900) AS count_transfers,
          courier_id,
          multiIf((merchant_name IN ('KFC', 'Бургер Кинг', 'ООО Интернет Решения подразделения'))
                  AND (order_on_time = 'Да')
                  AND (courier_delivered_at <= delivery_expected_time), 1, (merchant_name IN ('KFC', 'Бургер Кинг', 'ООО Интернет Решения подразделения'))
                  AND (order_on_time = 'Да')
                  AND (courier_delivered_at > delivery_expected_time), 2, (merchant_name IN ('KFC', 'Бургер Кинг', 'ООО Интернет Решения подразделения'))
                  AND (order_on_time = 'Нет')
                  AND (status_description = 'Заказ доставлен'), 3, status) AS on_time,
          area_id
   FROM mart.master_data_report),
                                        q AS
  (SELECT merchant_name,
          merchant_place_id,
          delivery_service_name,
          place_name,
          delivery_city_id,
          created_at,
          group_id,
          id,
          order_completion_date,
          courier_order_accepted_at,
          order_id,
          courier_id,
          status,
          wait_time,
          count_transfers,
          on_time,
          round(multiIf(flag_1 = 1, (b_1 - a_1) / 60, (courier_searched_at - a_1) / 60), 2) AS courier_searched_time,
          round(multiIf(flag_2 = 1, (courier_searched_at - a_2) / 60, (courier_order_accepted_at - courier_searched_at) / 60), 2) AS courier_accepted_time,
          round(multiIf(flag_3 = 1, (courier_searched_at - a_3) / 60, (courier_restaurant_arrived_at - courier_order_accepted_at) / 60), 2) AS courier_restaurant_arrived_time,
          round(multiIf(flag_4 = 1, (courier_restaurant_arrived_at - a_4) / 60, (courier_received_order_at - courier_restaurant_arrived_at) / 60), 2) AS courier_wait_restaurant_time,
          round(multiIf(flag_5 = 1, (courier_received_order_at - a_5) / 60, (courier_arrive_delivery_address_at - courier_received_order_at) / 60), 2) AS delivery_time,
          round(multiIf(flag_6 = 1, (courier_arrive_delivery_address_at - order_completion_date) / 60, (courier_delivered_at - courier_arrive_delivery_address_at) / 60), 2) AS handing_time,
          round(multiIf(isNull(order_started_at)
                        AND isNull(courier_searched_at)
                        AND (status != 301), (order_completion_date-created_at) / 60, a_7 / 60), 2) AS time_start_search_courier,
          order_started_at,
          area_id
   FROM r)
SELECT merchant_name,
       merchant_place_id,
       delivery_service_name,
       place_name,
       delivery_city_id,
       created_at,
       courier_order_accepted_at,
       order_completion_date,
       group_id,
       id,
       order_id,
       courier_id,
       status,
       wait_time,
       count_transfers,
       on_time,
       multiIf(courier_searched_time < 0, 0, courier_searched_time) AS courier_searched_time,
       multiIf(courier_accepted_time < 0, 0, courier_accepted_time) AS courier_accepted_time,
       multiIf(courier_restaurant_arrived_time < 0, 0, courier_restaurant_arrived_time) AS courier_restaurant_arrived_time,
       multiIf(courier_wait_restaurant_time < 0, 0, courier_wait_restaurant_time) AS courier_wait_restaurant_time,
       multiIf(delivery_time < 0, 0, delivery_time) AS delivery_time,
       multiIf(handing_time < 0, 0, handing_time) AS handing_time,
       multiIf(isNotNull(order_started_at)
               AND (order_started_at < created_at), 0, time_start_search_courier) AS time_start_search_courier,
       area_id
FROM q;