SET @date_from = '2019-04-15',
     @date_to = '2019-04-19';



SELECT  dt
            , segment
            , platform
            , customers_status
            , COUNT(count_customers) AS count_customers
            , COUNT(count_customers_selected_categories) AS count_customers_selected_categories
            , COUNT(count_orders) AS count_orders
            , COUNT(count_orders_selected_categories) AS count_orders_selected_categories
            , SUM(total_revenue) AS total_revenue
            , SUM(total_revenue_selected_categories) AS total_revenue_selected_categories

FROM (

        SELECT  DATE(opn.op_date_added) AS dt
                    , IFNULL(CONCAT(abt.ab_test_id, '_', abt.ab_test_part), '-') 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 platform
                    , cs.customers_status_name AS customers_status
                    , COUNT(DISTINCT o.customers_id) AS count_customers
                    , NULL AS count_customers_selected_categories
                    , COUNT(DISTINCT o.orders_id) AS count_orders
                    , NULL AS count_orders_selected_categories
                    , SUM(GREATEST(1 - opn.is_deleted,0) * opn.products_price) AS total_revenue
                    , 0 AS total_revenue_selected_categories
        FROM orders AS o
        LEFT JOIN orders_platform AS op ON op.orders_id = o.orders_id
        JOIN orders_products_new AS opn ON o.orders_id = opn.orders_id AND opn.op_date_added BETWEEN @date_from AND @date_to
        /*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
        GROUP BY    DATE(opn.op_date_added)
                    , IFNULL(CONCAT(abt.ab_test_id, '_', abt.ab_test_part), '-')
                    , op.platform
                    , CASE
                            WHEN op.platform IN ('ios_app','android_app') THEN 'Mamsy_app'
                            WHEN op.platform IN ('ios','android') THEN 'Mobile'
                            ELSE 'Desktop'
                      END
                    , cs.customers_status_name

            UNION ALL

        SELECT  DATE(opn.op_date_added) AS dt
                    , IFNULL(CONCAT(abt.ab_test_id, '_', abt.ab_test_part), '-') 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 platform
                    , cs.customers_status_name AS customers_status
                    , NULL AS count_customers
                    , COUNT(DISTINCT o.customers_id) AS count_customers_selected_categories
                    , NULL AS count_orders
                    , COUNT(DISTINCT o.orders_id) AS count_orders_selected_categories
                    , 0 AS total_revenue
                    , SUM(GREATEST(1 - opn.is_deleted,0) * opn.products_price) AS total_revenue_selected_categories
        FROM orders AS o
        LEFT JOIN orders_platform AS op ON op.orders_id = o.orders_id
        JOIN orders_products_new AS opn ON o.orders_id = opn.orders_id AND opn.op_date_added BETWEEN @date_from AND @date_to
        /*LEFT*/ JOIN categories_extra AS ce ON opn.products_categories_id = ce.categories_id AND ce.categories_hide_on_main = 0
        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
        GROUP BY    DATE(opn.op_date_added)
                    , IFNULL(CONCAT(abt.ab_test_id, '_', abt.ab_test_part), '-')
                    , op.platform
                    , CASE
                            WHEN op.platform IN ('ios_app','android_app') THEN 'Mamsy_app'
                            WHEN op.platform IN ('ios','android') THEN 'Mobile'
                            ELSE 'Desktop'
                      END
                    , cs.customers_status_name

        ) AS t
GROUP BY dt
            , segment
            , platform
            , customers_status
ORDER BY dt
            , segment
            , platform
            , customers_status