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

-- 思路：

-- 1.筛选出10月份的新用户

-- 2.按指标计算：所有新用户的首单平均交易金额（客单价）和平均获客成本并（保留一位小数)

-- 思路：
		-- 1.筛选出10月份的新用户
		-- 2.按指标计算：所有新用户的首单平均交易金额（客单价）和平均获客成本并（保留一位小数)

-- 1.筛选出10月份的新用户
with new_user as(
select
    uid,min(event_time)
from tb_order_overall
-- 10月的所有新用户：首次event_time在10月之内
group by uid having min(date(event_time)) between '2021-10-01' and '2021-10-31'
)

-- 2.按指标计算：所有新用户的首单平均交易金额（客单价）和平均获客成本并（保留一位小数)
select 
    round(sum(total_amount) / count(1), 1) as avg_amount,
    round(sum(cost) / count(1), 1) as avg_cost
from (
select 
    o.order_id,
    max(total_amount) as total_amount,
    sum(price*cnt)-max(total_amount) as cost
from tb_order_overall o 
join tb_order_detail d on o.order_id = d.order_id
where (uid, event_time) in (select * from new_user)
    and date_format(event_time, '%Y-%m') = '2021-10'
group by o.order_id
) t