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

with tiaojian as (
select 
uid,
order_id,
total_amount
from(
select 
uid,
order_id,
total_amount,
date_format(event_time,"%Y%m") as month,
dense_rank()over(partition by uid order by date(event_time) ) as pn
from tb_order_overall
) as t 
where
month="202110"
and
pn=1
),tiaojian1 as (
select 
order_id,
sum(price) as tp
from tb_order_detail
group by order_id
)


select 
round(
avg(total_amount),1),
round(
avg(tp-total_amount),1)
from tiaojian t 
inner join tiaojian1 t1
on t.order_id=t1.order_id