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

这里有一个误区，10月里的所有新用户，并不意味着10月里的下了第一单就是新用户，所以在新用户的判断上，还应该扫全表来判断，再在扫出来的结果里面选择10月份的用户。

select round(sum(total_amount) / count(order_id),1),
round(sum(get_cost) / count(order_id), 1)
from (
select
t1.order_id,
min(t2.total_amount) total_amount,
(sum(t1.price * t1.cnt) - min(t2.total_amount)) get_cost
from tb_order_detail t1 
left join (
select 
order_id,
total_amount,
event_time,
dense_rank() over (partition by uid order by event_time asc) rnk
from 
tb_order_overall
    ) t2 
    on t1.order_id = t2.order_id
    where t2.rnk = 1 and date(t2.event_time) between '2021-10-01' and '2021-10-31'
    group by 1 ) t3