注意10月份的商城新用户意味着用户的首单时间在2021.10

# 先计算2021年10月平台的新用户的首单订单信息,
# 此处新用户的定义是第一次下单时间在2021年10月份
with t1 as (
    select b.uid, a.order_id, a.total_amount
    from tb_order_overall as a
    right join (
        select *
        from (
            select uid, min(event_time) as first_time
            from tb_order_overall
            group by uid
        ) as e
        where date_format(first_time, '%Y-%m') = '2021-10'
    ) as b
    on b.uid = a.uid and b.first_time = a.event_time

),
t2 as (
    # 计算每一个用户的首单订单的优惠金额
    select t1.uid, t1.total_amount, d.total_cost - t1.total_amount as discount_money
    from t1
    left join (
        select t1.order_id, sum(c.price * c.cnt) as total_cost
        from t1
        left join tb_order_detail as c
        on c.order_id = t1.order_id
        group by t1.order_id
    ) as d
    on d.order_id = t1.order_id
)
select round(avg(total_amount), 1) as avg_amount, round(avg(discount_money), 1) as avg_cost
from t2;