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

with t0 as(
    select
    uid,
    order_id,
    sum(price*cnt)-avg(total_amount) as discont
    from tb_order_detail left join tb_order_overall using(order_id)
    where date_format(event_time,'%Y-%m')='2021-10'
    group by uid,order_id
),
t1 as(
    select
    uid,
    total_amount,
    # price*cnt-total_amount as discont,
    discont,
    ROW_NUMBER() over(partition by uid order by event_time asc) as tmp
    from tb_order_detail inner join tb_order_overall using(order_id)
    inner join tb_product_info using(product_id)
    inner join t0 using(uid,order_id)
    where date_format(event_time,'%Y-%m')='2021-10' and uid not in(
        select uid from tb_order_overall 
        where date_format(event_time,'%Y-%m')<'2021-10'
        )
    # group by uid
    # having tmp=1
)
select
round(sum(total_amount)/count(1),1) as avg_amount,
round(sum(discont)/count(1),1) as avg_cost
from t1
where tmp=1

全部评论

相关推荐

05-20 13:59
门头沟学院 Java
米黑子米黑子:你这个成绩不争取下保研?
点赞 评论 收藏
分享
点赞 评论 收藏
分享
兄弟们,实习都是在接各种api,该怎么包装简历
仁者伍敌:感觉我自己做小项目也是各种api啊,我要怎么包装简历
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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