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

【场景】：新用户首次行为

【分类】：聚合函数、子查询

分析思路

难点：

1.获取新用户首单的信息。使用in子查询

（1）统计每个订单的商品总金额

• [使用]：sum() group by

（2）计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）

**连续的表示：**日期减去排序的值相等；

• [条件]：2021年10月；新用户首单

• [使用]：(uid,event_time) in( )

最终结果

select 查询结果 [平均交易金额;平均获客成本]
from 从哪张表中查询数据[多表]
where 查询条件 [2021年10月;已付款：新用户首单]

求解代码

方法一：

with子句

with
    main as(
        #统计每个订单的商品总金额
        select
            order_id,
            sum(price*cnt) as uid_cost
        from tb_order_detail
        group by order_id
    )

#计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）
select
    round(avg(total_amount),1) as avg_amount,
    round(avg(uid_cost-total_amount),1) as avg_cost
from tb_order_overall
join main using(order_id)
where date_format(event_time,'%Y%m') = '202110'
and status = 1
and (uid,event_time) in(
    select
        uid,
        min(event_time) as event_time
    from tb_order_overall
    group by uid
)

方法二

多表嵌套

#计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）
select
    round(avg(total_amount),1) as avg_amount,
    round(avg(uid_cost-total_amount),1) as avg_cost
from tb_order_overall
join(
    #统计每个订单的商品总金额
    select
        order_id,
        sum(price*cnt) as uid_cost
    from tb_order_detail
    group by order_id
) main using(order_id)
where date_format(event_time,'%Y%m') = '202110'
and status = 1
and (uid,event_time) in(
    select
        uid,
        min(event_time) as event_time
    from tb_order_overall
    group by uid
)