ninjacn · July 25, 2017 02:41
diff --git a/gistfile1.txt b/gistfile1.txt
 select 
 t_x.refer_from,
 COALESCE(t_a.customers_count_for_refer,0) as customers_count_for_refer,
 COALESCE(t_b.order_count_for_first_order,0) as order_count_for_first_order,
 COALESCE(t_b.first_order_total_price,0) as first_order_total_price,
 COALESCE(t_c.customers_count_for_reg_and_order,0) as customers_count_for_reg_and_order,
 COALESCE(t_c.total_price_for_reg_and_order,0) as total_price_for_reg_and_order
 from
 (
 select c.refer_from from customers as c where
 c.refer_from <> ''
 group by c.refer_from
 ) as t_x
 left join
 (
 select 
 c.refer_from,
 count(c.id) customers_count_for_refer
 from customers as c
 inner join regionals as r on c.region=r.regional_key
 where
 #r.id in ('53','15') and
 c.refer_from <> ''
 and c.created_at between '2016-04-25 00:00:00' and '2016-04-26 00:00:00'
 #and c.store_type = 'BUSINESS'
 group by c.refer_from
 ) as t_a
 on t_x.refer_from=t_a.refer_from
 left join (
 select 
 c.refer_from,count(*)  as order_count_for_first_order,
 sum(o.snap_total_price) as first_order_total_price 
 from orders as o 
 inner join customers as c on o.`customer_id`=c.id
 inner join regionals as r on o.region=r.regional_key
 where 
 c.refer_from <> ''
 #and c.store_type = 'BUSINESS'
 and o.`is_customer_first_order`='1' 
 #and r.id in ('53','15') 
 and o.pay_status!='NOPAY'
 and o.created_at between '2016-04-25 00:00:00' and '2016-04-26 00:00:00'
 group by c.refer_from
 ) as t_b
 on t_x.refer_from=t_b.refer_from
 left join (
 select 
 c.refer_from,
 count(o.id) as customers_count_for_reg_and_order,
 sum(o.snap_total_price) as total_price_for_reg_and_order
 from orders as o
 inner join customers as c on o.customer_id=c.id 
 inner join regionals as r on o.region=r.regional_key
 where
 c.refer_from <> ''
 #and r.id in ('53','15') 
 #and c.store_type = 'BUSINESS'
 and o.pay_status!='NOPAY'
 and o.`is_customer_first_order`='1' 
 and o.created_at between '2016-04-25 00:00:00' and '2016-04-26 00:00:00'
 and c.created_at between '2016-04-25 00:00:00' and '2016-04-26 00:00:00'
 group by c.refer_from
 ) as t_c
 on t_x.refer_from=t_c.refer_from
 where customers_count_for_refer>0 or order_count_for_first_order>0 or customers_count_for_reg_and_order>0
 order by customers_count_for_refer desc
	select
	t_x.refer_from,
	COALESCE(t_a.customers_count_for_refer,0) as customers_count_for_refer,
	COALESCE(t_b.order_count_for_first_order,0) as order_count_for_first_order,
	COALESCE(t_b.first_order_total_price,0) as first_order_total_price,
	COALESCE(t_c.customers_count_for_reg_and_order,0) as customers_count_for_reg_and_order,
	COALESCE(t_c.total_price_for_reg_and_order,0) as total_price_for_reg_and_order
	from
	(
	select c.refer_from from customers as c where
	c.refer_from <> ''
	group by c.refer_from
	) as t_x
	left join
	(
	select
	c.refer_from,
	count(c.id) customers_count_for_refer
	from customers as c
	inner join regionals as r on c.region=r.regional_key
	where
	#r.id in ('53','15') and
	c.refer_from <> ''
	and c.created_at between '2016-04-25 00:00:00' and '2016-04-26 00:00:00'
	#and c.store_type = 'BUSINESS'
	group by c.refer_from
	) as t_a
	on t_x.refer_from=t_a.refer_from
	left join (
	select
	c.refer_from,count(*) as order_count_for_first_order,
	sum(o.snap_total_price) as first_order_total_price
	from orders as o
	inner join customers as c on o.`customer_id`=c.id
	inner join regionals as r on o.region=r.regional_key
	where
	c.refer_from <> ''
	#and c.store_type = 'BUSINESS'
	and o.`is_customer_first_order`='1'
	#and r.id in ('53','15')
	and o.pay_status!='NOPAY'
	and o.created_at between '2016-04-25 00:00:00' and '2016-04-26 00:00:00'
	group by c.refer_from
	) as t_b
	on t_x.refer_from=t_b.refer_from
	left join (
	select
	c.refer_from,
	count(o.id) as customers_count_for_reg_and_order,
	sum(o.snap_total_price) as total_price_for_reg_and_order
	from orders as o
	inner join customers as c on o.customer_id=c.id
	inner join regionals as r on o.region=r.regional_key
	where
	c.refer_from <> ''
	#and r.id in ('53','15')
	#and c.store_type = 'BUSINESS'
	and o.pay_status!='NOPAY'
	and o.`is_customer_first_order`='1'
	and o.created_at between '2016-04-25 00:00:00' and '2016-04-26 00:00:00'
	and c.created_at between '2016-04-25 00:00:00' and '2016-04-26 00:00:00'
	group by c.refer_from
	) as t_c
	on t_x.refer_from=t_c.refer_from
	where customers_count_for_refer>0 or order_count_for_first_order>0 or customers_count_for_reg_and_order>0
	order by customers_count_for_refer desc