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

with t1 as(
select uid,order_id,date(event_time) as dt,total_amount,price,cnt,
dense_rank() over( partition by uid order by date(event_time)) as rn
from tb_order_overall
join tb_order_detail using(order_id)
),
t2 as (
select * from t1 where rn=1),

t3 as(
select uid,order_id,total_amount,dt,sum(price*cnt)-total_amount as cost
from t2 
where DATE_FORMAT(dt,'%Y%m')='202110'
group by uid,order_id,total_amount,dt
)

select round(avg(total_amount),1) as avg_amount,
round(avg(cost),1) as avg_cost from t3