题解 | 10月的新户客单价和获客成本

# 链接表，得到uid,order_id,total_amount,total-zong
with t as (
    select x.uid,z.order_id,o.total_amount,z.zong-o.total_amount as cost
from 
    (
        # 找出新客人
        select uid,min(event_time) as zuichu
        from tb_order_overall
        group by uid
        having date_format(zuichu,'%Y-%m')='2021-10'
    ) as x
    inner join tb_order_overall as o on o.uid=x.uid and o.event_time=x.zuichu
    inner join 
        (
            # 得到商品的总金额
            select order_id,sum(price*cnt) as zong
            from tb_order_detail
            group by order_id
        ) as z on o.order_id=z.order_id
)
select round(avg(t.total_amount),1) as avg_amount,round(avg(t.cost),1) as avg_cost
from t