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

with t1 as (select uid,
min(date(event_time)) as min_t
from tb_order_overall tl
group by uid
having min_t between '2021-10-01' and '2021-10-31'
)
,
t2 as (
select *
from(
select t1.uid,
order_id,
event_time,
total_amount,
row_number() over(partition by uid order by event_time) as ranks
from t1 
left join tb_order_overall tl 
on t1.uid=tl.uid) a 
where ranks =1
)

select
    round(avg(total_amount),1) as avg_amount,
    round(avg(cost),1) as avg_cost
    from
(
select t2.order_id,
total_amount,
total_p,
total_p-total_amount as cost
from t2
left join(
select t2.order_id,
sum(price) as total_p
from t2 
left join tb_order_detail td
on t2.order_id=td.order_id
group by t2.order_id
) b
on t2.order_id=b.order_id
) c