SELECT  DATE(opn.op_date_added) AS date
        , IFNULL(abt.ab_test_part,0) AS segment
        , CASE
                WHEN op.platform IN ('ios_app','android_app') THEN 'Mamsy_app'
                WHEN op.platform IN ('ios','android') THEN 'Mobile'
                ELSE 'Desktop'
          END AS platform2
        , cs.customers_status_name AS customers_status
        , COUNT(DISTINCT o.customers_id)  AS count_customers
        , COUNT(DISTINCT IF(ce.categories_hide_on_main = 0, o.customers_id, NULL)) AS count_customers_selected_categories
        ,  COUNT(DISTINCT o.orders_id)  AS count_orders
        , COUNT(DISTINCT IF(ce.categories_hide_on_main = 0,o.orders_id, NULL)) AS count_orders_selected_categories
        , SUM(GREATEST(1 - opn.is_deleted,0) * opn.products_price) AS total_revenue
        , SUM(IF(IFNULL(ce.categories_hide_on_main, 0) = 0, GREATEST(1 - opn.is_deleted,0) * opn.products_price, 0)) AS total_revenue_selected_categories
        , SUM(IF(IFNULL(ce.categories_hide_on_main, 0) = 1, GREATEST(1 - opn.is_deleted,0) * opn.products_price, 0)) AS total_revenue_hide_categories
  FROM orders AS o

     JOIN orders_products_new AS opn ON o.orders_id = opn.orders_id
     LEFT JOIN orders_platform AS op ON op.orders_id = o.orders_id
     LEFT JOIN categories_extra AS ce ON opn.products_categories_id = ce.categories_id
     LEFT JOIN customers AS c ON o.customers_id = c.customers_id
     LEFT JOIN customers_status AS cs ON c.customers_status = cs.customers_status_id
     LEFT JOIN customers_to_ab_testing AS ctabt ON c.customers_id = ctabt.customers_id
     LEFT JOIN ab_testing AS abt ON abt.session_id = ctabt.ab_testing_session_id AND abt.ab_test_id = 4
   WHERE o.orders_status != 18
    AND opn.op_date_added BETWEEN '2019-04-16' AND '2019-04-19'
  GROUP BY
              segment
              , platform2
          --    , cs.customers_status_name
--               , date
          ORDER BY platform2, segment