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

with a as(
select * from (
select *, row_number()over(partition by uid order by event_time asc) as rn from tb_order_overall
) z
where rn = 1 and year(event_time) = 2021 and month (event_time) = 10),

b as(select order_id, sum(price*cnt) as 总价 from tb_order_detail
group by order_id)

select round(sum(a.total_amount)/count(*) , 1) as 首单平均交易金额, round((sum(b.总价) - sum(a.total_amount))/count(*), 1) as 平均获客成本
from a left join b 
on a. order_id = b.order_id