Skip to content

Instantly share code, notes, and snippets.

@ninjacn

ninjacn/gist:2b8c2df2a6f32e21ad316d64e544cfe0

Created July 25, 2017 02:41
Show Gist options
  • Save ninjacn/2b8c2df2a6f32e21ad316d64e544cfe0 to your computer and use it in GitHub Desktop.
Save ninjacn/2b8c2df2a6f32e21ad316d64e544cfe0 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ninjacn created this gist Jul 25, 2017.
    66 changes: 66 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,66 @@
    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