题解 | #10月的新户客单价和获客成本#
10月的新户客单价和获客成本
http://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64
思路：
变量选取
- 2021年10月所有——以月份分组计算
- date_format(event_time,'%Y%m') as month
- where month='202110
- group by month
- 2021年10月新用户——首单在2021.10
- min(event_time) as first_time在2021.10
- 首单
- event_time=first_time
- 订单优惠金额
- 应付：sum(price*cnt) as total_price group by order_id
- 实付：total_amout
步骤拆分
- 筛选首单信息
- 用于筛选符合条件的首单需要的month,first_time
- 用于计算金额的total price,total_amout
- 前者group by uid，后者group by order_id，故可用聚合窗口函数，互不干扰
- 计算平均首单客单价和平均获客成本
- avg取平均
- group by month
代码
select round(avg((total_amount)),1) as avg_amount,
round(avg(total_price-total_amount),1) as avg_cost
from
(select distinct tod.order_id,event_time,total_amount,
date_format(event_time,'%Y%m') as month,
min(event_time) over (partition by uid) as first_time,
sum(price*cnt) over (partition by tod.order_id) as total_price
from tb_order_detail as tod
join tb_order_overall as too
on tod.order_id=too.order_id) as q
where month='202110' and event_time=first_time
group by month
，
查看19道真题和解析