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

# 请计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）。
# 订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。
with t as (
    -- 首单

    select
            order_id
            ,total_amount
            ,event_time
    from
    (
    #     DATE_FORMAT(event_time,'%Y-%m')='2021-10' 为什么这个东西要放在外面筛选？
    #     解答：题目问的是2021年10月商城里所有新用户，若一个用户在9月为新订单，如果放在里面先筛选的话，这条9月的信息就会被筛选掉，那么就会默认10月份那个算最新的，反而会加入错误数据
    #     若题目问的是每个用户十月的第一个订单，那么就可以把DATE_FORMAT(event_time,'%Y-%m')='2021-10'放在里面
        select
            *
            ,dense_rank() over(partition by uid order by event_time) as rk
        from tb_order_overall
    ) tmp_table
    where DATE_FORMAT(event_time,'%Y-%m')='2021-10' and rk=1

),t2 as (
    select
        order_id,
        sum(price*cnt) as origin_pay -- 未优惠的原价
    from tb_order_detail
    group by order_id
),t3 as (
    select
        t.*,
        t2.origin_pay,
        (t2.origin_pay - t.total_amount) as discount_fee -- 优惠金额
    from t2 join t on t2.order_id=t.order_id
)
select
    round(sum(total_amount)/count(order_id),1) as avg_amount,
    round(sum(discount_fee)/count(*),1) as avg_cost
from t3