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

with tb_order_detail_total as -- 先把明细表的金额根据订单求出,方便汇入主表
         (
             select order_id,
                    sum(price * cnt) origin_amount
             from tb_order_detail
             group by order_id
         )

, tb_order_sort as -- 整合主表和明细表,并标出每个订单是该用户的第几单
         (
             select
                    event_time,
                    total_amount,
                    origin_amount,
                    dense_rank() over (partition by uid order by event_time asc) drk_order
             from tb_order_overall
                    left join tb_order_detail_total using (order_id)
             where (status = '0'or status = '1')
         )

-- 然后找出2021年10月份的订单,并且其中是首单的
-- 需要注意一定是先排序才能筛选,不然会添加异常新用户数据
-- 求对应均值即可
select ifnull(round(sum(total_amount) / count(*), 1), 0) as avg_amount,
       ifnull(round(sum(origin_amount - total_amount) / count(*), 1), 0) as avg_cost
from tb_order_sort
where drk_order = 1
and date_format(event_time, '%Y%m') = '202110';