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

10月的新户客单价和获客成本

https://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64

# 先计算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;

全部评论

相关推荐

05-26 09:07
已编辑
门头沟学院 Java
点赞 评论 收藏
分享
群星之怒:不是哥们,你就不好奇瘫痪三十年的老植物人是啥样的吗?
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务