SHARE
TWEET

Untitled

a guest Apr 18th, 2019 80 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET @date_from = '2019-04-15',
  2.      @date_to = '2019-04-19';
  3.  
  4. WITH segment_customers
  5. AS (
  6.         SELECT  CONCAT(abt.ab_test_id, '_', abt.ab_test_part) AS segment
  7.                     , IF(LEFT(abt.user_agent,8) = 'MamsyApp'
  8.                         , 'MamsyApp'
  9.                         , IF(abt.is_mobile = 1, 'Mobile', 'Desktop')
  10.                         ) AS platform
  11.                     , ctabt.customers_id
  12.                     , cs.customers_status_name AS customers_status
  13.         FROM ab_testing AS abt
  14.         JOIN customers_to_ab_testing AS ctabt ON abt.session_id = ctabt.ab_testing_session_id
  15.         JOIN customers AS c ON ctabt.customers_id = c.customers_id
  16.         JOIN customers_status AS cs ON c.customers_status = cs.customers_status_id
  17.         WHERE abt.ab_test_id = 4
  18.     )
  19.  
  20. , segment_total
  21. AS (
  22.         SELECT  DATE(opn.op_date_added) AS dt
  23.                     , sc.segment
  24.                     , IF(op.platform IN ('ios_app','android_app'), 'Mobile', 'Desktop') AS platform
  25.                     , sc.customers_status
  26.                     , opn.products_categories_id AS categories_id
  27.                     , ce.categories_hide_on_main
  28.                     , COUNT(DISTINCT o.customers_id) AS count_customers
  29.                     , NULL AS count_customers_selected_categories
  30.                     , COUNT(DISTINCT o.orders_id) AS count_orders
  31.                     , NULL AS count_orders_selected_categories
  32.                     , SUM(GREATEST(1 - opn.is_deleted,0) * opn.products_price) AS total_revenue
  33.                     , 0 AS total_revenue_selected_categories
  34.         FROM segment_customers AS sc
  35.         JOIN orders AS o ON sc.customers_id = o.customers_id  -- AND o.date_purchased BETWEEN @date_from AND @date_to
  36.         LEFT JOIN orders_platform AS op ON op.orders_id = o.orders_id
  37.         JOIN orders_products_new AS opn ON o.orders_id = opn.orders_id AND opn.op_date_added BETWEEN @date_from AND @date_to
  38.         /*LEFT*/ JOIN categories_extra AS ce ON opn.products_categories_id = ce.categories_id
  39.         GROUP BY    DATE(opn.op_date_added)
  40.                     , sc.segment
  41.                     , IF(op.platform IN ('ios_app','android_app'), 'Mobile', 'Desktop')
  42.                     , sc.customers_status
  43.                     , opn.products_categories_id
  44.                     , ce.categories_hide_on_main
  45.                    
  46.             UNION ALL
  47.            
  48.         SELECT  DATE(opn.op_date_added) AS dt
  49.                     , sc.segment
  50.                     , IF(op.platform IN ('ios_app','android_app'), 'Mobile', 'Desktop') AS platform
  51.                     , sc.customers_status
  52.                     , opn.products_categories_id AS categories_id
  53.                     , ce.categories_hide_on_main
  54.                     , NULL AS count_customers
  55.                     , COUNT(DISTINCT o.customers_id) AS count_customers_selected_categories
  56.                     , NULL AS count_orders
  57.                     , COUNT(DISTINCT o.orders_id) AS count_orders_selected_categories
  58.                     , 0 AS total_revenue
  59.                     , SUM(GREATEST(1 - opn.is_deleted,0) * opn.products_price) AS total_revenue_selected_categories
  60.         FROM segment_customers AS sc
  61.         JOIN orders AS o ON sc.customers_id = o.customers_id  -- AND o.date_purchased BETWEEN @date_from AND @date_to
  62.         LEFT JOIN orders_platform AS op ON op.orders_id = o.orders_id
  63.         JOIN orders_products_new AS opn ON o.orders_id = opn.orders_id AND opn.op_date_added BETWEEN @date_from AND @date_to
  64.         /*LEFT*/ JOIN categories_extra AS ce ON opn.products_categories_id = ce.categories_id AND ce.categories_hide_on_main = 0
  65.         GROUP BY    DATE(opn.op_date_added)
  66.                     , sc.segment
  67.                     , IF(op.platform IN ('ios_app','android_app'), 'Mobile', 'Desktop')
  68.                     , sc.customers_status
  69.                     , opn.products_categories_id
  70.                     , ce.categories_hide_on_main           
  71.        
  72.         )
  73.  
  74. SELECT  dt
  75.             , segment
  76.             , platform
  77.             , customers_status
  78.             , categories_id
  79.             , COUNT(count_customers) AS count_customers
  80.             , COUNT(count_customers_selected_categories) AS count_customers_selected_categories
  81.             , COUNT(count_orders) AS count_orders
  82.             , COUNT(count_orders_selected_categories) AS count_orders_selected_categories
  83.             , SUM(total_revenue) AS total_revenue
  84.             , SUM(total_revenue_selected_categories) AS total_revenue_selected_categories
  85. FROM segment_total
  86. GROUP BY dt
  87.             , segment
  88.             , platform
  89.             , customers_status
  90.             , categories_id
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top