user.rb

      SELECT replace(users.first_name,',',' ') AS 'FirstName',
      replace(users.last_name,',',' ') AS 'LastName',
      users.created_at AS 'SignUpDate',
      users.cancelled_membership_at AS 'MembershipCancelledDate'," +
                         newsletter_queries +
            "
                          (SELECT count(orders.id)
                            FROM orders
                            WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered')
                            AND orders.user_id=users.id) as NumberOfOrders,
                          (SELECT count(orders.id)
                             FROM orders
                             WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id) AS NumberOfSubscriptions,
                          (SELECT (case when MIN(orders.user_selected_ship_date) is not null then 1 else 0 end)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0001') AS 'HasDiapersSubscription',
                          (SELECT MIN(orders.user_selected_ship_date)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0001') AS 'NextDiaperShipment',
                          (SELECT (case when MIN(orders.user_selected_ship_date) is not null then 1 else 0 end)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0002') AS 'HasEssentialsSubscription',
                          (SELECT MIN(orders.user_selected_ship_date)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0002') AS 'NextEssentialsShipment',
                          (SELECT (case when MIN(orders.user_selected_ship_date) is not null then 1 else 0 end)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0003') AS 'HasHWSubscription',
                          (SELECT MIN(orders.user_selected_ship_date)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0003') AS 'NextHWShipment',
                          (SELECT (case when MIN(orders.user_selected_ship_date) is not null then 1 else 0 end)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0004') AS 'HasFeedingSubscription',
                          (SELECT MIN(orders.user_selected_ship_date)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state='start_order' AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0004') AS 'NextFeedingShipment',
                          (SELECT count(orders.id) > 0
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.order_classification = 2
                             AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered')
                             AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=19) AS 'DiapersTrial',
                          (SELECT local_date(max(order_state_transitions.created_at))
                             FROM orders
                             JOIN order_state_transitions on (orders.id=order_state_transitions.order_id and order_state_transitions.event='start_vanity')
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.order_classification = 2
                             AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered')
                             AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=19
                             GROUP BY orders.user_id) AS 'DiapersTrialDate',
                          (SELECT count(orders.id) > 0
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.order_classification = 2
                             AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered')
                             AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=16) AS 'EssentialsTrial',
                          (SELECT local_date(max(order_state_transitions.created_at))
                             FROM orders
                             JOIN order_state_transitions on (orders.id=order_state_transitions.order_id and order_state_transitions.event='start_vanity')
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.order_classification = 2
                             AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered')
                             AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=16
                             GROUP BY orders.user_id) AS 'EssentialsTrialDate',
                          (SELECT count(orders.id) > 0
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.order_classification = 2
                             AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered')
                             AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=84) AS 'HWTrial',
                          (SELECT local_date(max(order_state_transitions.created_at))
                             FROM orders
                             JOIN order_state_transitions on (orders.id=order_state_transitions.order_id and order_state_transitions.event='start_vanity')
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.order_classification = 2
                             AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered')
                             AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=84
                             GROUP BY orders.user_id) AS 'HWTrialDate',
                          (SELECT count(orders.id) > 0
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.order_classification = 2
                             AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered')
                             AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=206) AS 'FeedingTrial',
                          (SELECT local_date(max(order_state_transitions.created_at))
                             FROM orders
                             JOIN order_state_transitions on (orders.id=order_state_transitions.order_id and order_state_transitions.event='start_vanity')
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.order_classification = 2
                             AND orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered')
                             AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.product_id=206
                             GROUP BY orders.user_id) AS 'FeedingTrialDate',
                          (SELECT count(orders.id)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0001') AS 'DiapersSubscriptionOrders',
                          (SELECT count(orders.id)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0002') AS 'EssentialsSubscriptionOrders',
                          (SELECT count(orders.id)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0003') AS 'HWSubscriptionOrders',
                          (SELECT count(orders.id)
                             FROM orders
                             LEFT OUTER JOIN line_items ON line_items.itemizable_id=orders.id
                             LEFT OUTER JOIN variants ON line_items.variant_id=variants.id
                             WHERE orders.state in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (0,1,7,8,10) AND orders.user_id=users.id AND line_items.itemizable_type='Order' AND variants.sku='0004') AS 'FeedingSubscriptionOrders',
                          (SELECT (case when user_trans.subscription like '%Diaper%' then null else max(end_date) end)
                            from subscriptions_plus_minus
                            join user_trans on (subscriptions_plus_minus.user_id=user_trans.user_id
                            and product_type='Diaper Bundle')
                            where user_trans.user_id=users.id
                           ) 'DiaperCancelDate',
                          (SELECT (case when user_trans.subscription like '%Essentials%' then null else max(end_date) end)
                            from subscriptions_plus_minus
                            join user_trans on (subscriptions_plus_minus.user_id=user_trans.user_id
                            and product_type='Essentials Bundle')
                            where user_trans.user_id=users.id
                           ) 'EssentialsCancelDate',
                          (SELECT (case when user_trans.subscription like '%H&W%' then null else max(end_date) end)
                            from subscriptions_plus_minus
                            join user_trans on (subscriptions_plus_minus.user_id=user_trans.user_id
                            and product_type='H&W Bundle')
                            where user_trans.user_id=users.id
                           ) 'HWCancelDate',
                          (SELECT (case when user_trans.subscription like '%Feeding%' then null else max(end_date) end)
                            from subscriptions_plus_minus
                            join user_trans on (subscriptions_plus_minus.user_id=user_trans.user_id
                            and product_type='Feeding Bundle')
                            where user_trans.user_id=users.id
                           ) 'FeedingCancelDate',
                          (SELECT MAX(date_sub(orders.created_at,interval 8 hour))
                            FROM orders
                            WHERE orders.order_classification IN (5,9) and orders.state  in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.user_id=users.id) AS 'LastStorePurchase',
                          (SELECT count(orders.id)
                            FROM orders
                            WHERE orders.state  in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (5,9) AND orders.user_id=users.id) AS 'ShopOrders',
                          (SELECT count(orders.id)
                            FROM orders
                            WHERE orders.state  in ('charged','staged','pick_started','restaged','vanity_started','shipped','delivered') AND orders.order_classification IN (6, 14, 15) AND orders.user_id=users.id) AS 'GiftOrders',
                          (select count(distinct id)  from test.order_product dr1
                            where dr1.user_id=users.id and product_id =1
                            group by bundle_id) 'PurchasedDiaperProducts',
                          (select max(order_date) from test.order_product
                            dr1 where dr1.user_id=users.id and product_id =1
                            group by user_id) 'LastDatePurchasedDiaperProducts',
                          (select count(distinct id) from test.order_product
                            where test.order_product.user_id=users.id and bundle_id=21) 'PurchasedEssentialsProducts',
                          (select max(order_date) from test.order_product
                            dr1  where dr1.user_id=users.id and bundle_id=21
                            group by user_id) 'LastDatePurchasedEssentialsProducts',
                          (select count(distinct id) from test.order_product
                            where test.order_product.user_id=users.id and bundle_id=73) 'PurchasedHWProducts',
                          (select max(order_date) from test.order_product
                            dr1  where dr1.user_id=users.id and bundle_id=73
                            group by user_id) 'LastDatePurchasedHWProducts',
                          (select count(distinct id) from test.order_product
                            where test.order_product.user_id=users.id and bundle_id=201) 'PurchasedFeedingProducts',
                          (select max(order_date) from test.order_product
                            dr1  where dr1.user_id=users.id and bundle_id=201
                            group by user_id) 'LastDatePurchasedFeedingProducts',
                          (SELECT count(*) FROM line_items join test.max_shop_order dr1 on (line_items.itemizable_id=dr1.id)
                            join variants on (line_items.variant_id=variants.id  and product_id=1)
                            left join bundle_add_ons on (variants.id=bundle_add_ons.variant_id) where dr1.user_id=users.id
                            ) as 'LastShopOrderDiaperProduct',
                          (SELECT count(*) FROM line_items  join test.max_shop_order dr1 on (line_items.itemizable_id=dr1.id)
                            join variants on (line_items.variant_id=variants.id)
                            join bundle_add_ons on (variants.id=bundle_add_ons.variant_id and bundle_id=21) where dr1.user_id=users.id
                            ) as 'LastShopOrderEssentialsProduct',
                          (SELECT count(*) FROM line_items join test.max_shop_order dr1 on (line_items.itemizable_id=dr1.id)
                            join variants on (line_items.variant_id=variants.id)
                            join bundle_add_ons on (variants.id=bundle_add_ons.variant_id and bundle_id=73) where dr1.user_id=users.id
                            ) as 'LastShopOrderHWProduct',
                          (SELECT count(*) FROM line_items join test.max_shop_order dr1 on (line_items.itemizable_id=dr1.id)
                            join variants on (line_items.variant_id=variants.id)
                            join bundle_add_ons on (variants.id=bundle_add_ons.variant_id and bundle_id=201) where dr1.user_id=users.id
                            ) as 'LastShopOrderFeedingProduct',
                          (SELECT max(date_sub(orders.updated_at,interval 8 hour))
                            FROM orders
                            WHERE orders.state IN ('cart') AND DATE(orders.updated_at + INTERVAL 2 DAY) < NOW() AND orders.user_id=users.id) AS 'LastCartAbandonDate',
                          (SELECT referrals.m_id FROM referrals WHERE referrals.user_id=users.id and referrals.campaign IS NULL) AS 'MID',
                          (SELECT referrals.a_id FROM referrals WHERE referrals.user_id=users.id and referrals.campaign IS NULL) AS 'AID',
                           users.match_indicator,
                           users.value_segment,
                           users.value_score,
                           date_sub(users.current_sign_in_at,interval 8 hour) 'LastSignIn',
                           users.email AS 'Email'
                           FROM users join changed_users on (users.id=changed_users.user_id);"