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

WITH segment_customers
AS (
        SELECT  CONCAT(abt.ab_test_id, '_', abt.ab_test_part) AS segment
                    , IF(LEFT(abt.user_agent,8) = 'MamsyApp'
                        , 'MamsyApp'
                        , IF(abt.is_mobile = 1, 'Mobile', 'Desktop')
                        ) AS platform
                    , ctabt.customers_id
                    , cs.customers_status_name AS customers_status
        FROM ab_testing AS abt
        JOIN customers_to_ab_testing AS ctabt ON abt.session_id = ctabt.ab_testing_session_id
        JOIN customers AS c ON ctabt.customers_id = c.customers_id
        JOIN customers_status AS cs ON c.customers_status = cs.customers_status_id
        WHERE abt.ab_test_id = 4
    )

, segment_total
AS (
        SELECT  sc.segment
                    , sc.platform
                    , sc.customers_status
                    , opn.products_categories_id AS categories_id
                    , ce.categories_hide_on_main
                    , COUNT(DISTINCT o.customers_id) AS count_customers
                    , COUNT(DISTINCT o.orders_id) AS count_orders
                    , SUM(GREATEST(1 - opn.is_deleted,0) * opn.products_price) AS orders_amount
        FROM segment_customers AS sc
        JOIN orders AS o ON sc.customers_id = o.customers_id  -- AND o.date_purchased BETWEEN @date_from AND @date_to
        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
        GROUP BY    sc.segment
                    , sc.platform
                    , sc.customers_status
                    , opn.products_categories_id
                    , ce.categories_hide_on_main
        )

SELECT *
FROM segment_total