题解 | #10月的新户客单价和获客成本#
10月的新户客单价和获客成本
https://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64
思路：
1；拿到新用户
row_number()over(partition by uid order by event_time)as frank 取frank=1
2、计算优惠金额的被减数
sum(price*cnt)
3、根据公式算
过程遇到的问题：
统计新用户时加了where 条件（2021-10）导致错误，题目要求是新用户在10月购买，而不是在十月产生的新用户，所以此处不能加这个过滤条件
select
uid,order_id,total_amount,event_time,
row_number()over(partition by uid order by event_time)as frank
from tb_order_overall
uid,order_id,total_amount,event_time,
row_number()over(partition by uid order by event_time)as frank
from tb_order_overall
where date_format(event_time,'%Y-%m')='2021-10'
select round(avg(total_amount),1)as avg_amount, round(avg(被减数-total_amount),1)as avg_cost from ( select a.order_id,total_amount,sum(price*cnt)as 被减数 from ( select uid,order_id,total_amount,event_time, row_number()over(partition by uid order by event_time)as frank from tb_order_overall )a left join tb_order_detail b on a.order_id=b.order_id where frank=1 and date_format(event_time,'%Y-%m')='2021-10' group by a.order_id,total_amount )a
投递字节跳动等公司9个岗位 >
查看19道真题和解析