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

with t1 as
(
    select 
    first_value(order_id)over(partition by uid order by date(event_time)) first_orderid
    from tb_order_overall
    where status = 1
)  -- 用户首单id

select
round(avg(total_amount),1) avg_amount
,round(avg(nominal_amount-total_amount),1) avg_cost
from
(
    select
    order_id
    ,avg(total_amount) total_amount  -- 有笛卡尔积产生，需要取均值
    ,sum(price*cnt) nominal_amount  -- 一个order_id对应多个product，要做一次聚合
    from tb_order_overall join tb_order_detail using(order_id)
    where order_id in (select * from t1)
    and date(event_time) like '2021-10%'  -- 10月新用户首单
    group by 1
) t2