Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 DATE(opn.op_date_added) AS dt
- , sc.segment
- , IF(op.platform IN ('ios_app','android_app'), 'Mobile', 'Desktop') AS platform
- , sc.customers_status
- , opn.products_categories_id AS categories_id
- , ce.categories_hide_on_main
- , 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 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
- 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
- GROUP BY DATE(opn.op_date_added)
- , sc.segment
- , IF(op.platform IN ('ios_app','android_app'), 'Mobile', 'Desktop')
- , sc.customers_status
- , opn.products_categories_id
- , ce.categories_hide_on_main
- UNION ALL
- SELECT DATE(opn.op_date_added) AS dt
- , sc.segment
- , IF(op.platform IN ('ios_app','android_app'), 'Mobile', 'Desktop') AS platform
- , sc.customers_status
- , opn.products_categories_id AS categories_id
- , ce.categories_hide_on_main
- , 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 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
- 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
- GROUP BY DATE(opn.op_date_added)
- , sc.segment
- , IF(op.platform IN ('ios_app','android_app'), 'Mobile', 'Desktop')
- , sc.customers_status
- , opn.products_categories_id
- , ce.categories_hide_on_main
- )
- SELECT dt
- , segment
- , platform
- , customers_status
- , categories_id
- , 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 segment_total
- GROUP BY dt
- , segment
- , platform
- , customers_status
- , categories_id
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement