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

明确题意：

请计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本

问题分解：

查找2021年10月每个用户的首单交易记录：rank()开窗后取rk = 1 and date_format(event_time, '%Y-%m') = '2021-10'

关联首单交易表和订单明细表：join tb_order_detail tod on t1.order_id = tod.order_id

按订单id分组：group by order_id,total_amount

计算订单原价：sum(price * cnt)

计算平均交易金额：avg(total_amount)

计算平均获客成本：avg(detail_amount - total_amount)

细节问题：

保留一位小数：round(x,1)

select
    round(avg(total_amount), 1) avg_amount,
    round(avg(detail_amount - total_amount), 1) avg_cost
from(
select
    t1.order_id,
    sum(price * cnt) detail_amount,
    total_amount
from(
select
    order_id,
    uid,
    event_time,
    total_amount,
    rank() over(partition by uid order by event_time) rk
from tb_order_overall
where status = 1
) t1
join tb_order_detail tod on t1.order_id = tod.order_id
where rk = 1 and date_format(event_time, '%Y-%m') = '2021-10'
group by order_id,total_amount
) t2;