题解 | #10月的新户客单价和获客成本#
10月的新户客单价和获客成本
https://www.nowcoder.com/practice/d15ee0798e884f829ae8bd27e10f0d64
# 订单优惠金额：理论订单总金额 - 实际订单总金额
# 求2021.10的所有新用户的的首单平均金额和平均获客成本
# 新用户的首单，也就是每个用户第一次出现的记录的订单
# 先对订单总表筛选符合时间要求和新用户首单要求的记录，在和明细表联结
# 计算 实际订单总金额 和 理论订单总金额
# 求均值，计算优惠也求均值
# 临时表，查询订单id，用户id，订单实际总金额，商品id，单价，卖出数量
WITH temp_0 AS(
SELECT order_id, uid, total_amount, product_id, price, cnt
# 是由两个子查询得到了的，只需要用后两个表。先要找到新用户的首单，我是用时间来确定的，一定要注意虽然求的是10月份的，但不是从10月份初始化的，要考虑到如果10月前有新用户存在
FROM(
SELECT *, MIN(event_time) OVER(PARTITION BY uid ORDER BY event_time) first_time
FROM tb_order_overall
# 这里就错误地将10月份作为启动月份了，一直有用例通不过
# WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-31' AND status = 1
) a
JOIN tb_order_detail b USING(order_id)
# 在联结两个表，得到每个用户的最早登录时间后，再筛选，这样当遇到10月份之前活跃的用户时也能成功识别出来
WHERE event_time = first_time AND DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-31' AND status = 1
)
# 主查询，求均值即可
SELECT ROUND(AVG(t_amount), 1) avg_amount, ROUND(AVG(l_amount - t_amount), 1) avg_cost
FROM(
# 子查询，以订单分组，求每个订单的实际总金额，理论总金额
SELECT MAX(total_amount) t_amount, SUM(price * cnt) l_amount
FROM temp_0
GROUP BY order_id
) a;
踩的坑：
在查询订单总和表时直接筛选了时间，然而可能存在10月份之前的记录
关键区别
- 正确的逻辑：先确定每个用户的首单时间，然后筛选在指定日期范围内的首单，确保了即使用户在10月之前有过订单，只有他们的实际首单才会被考虑。
- 错误的逻辑：在筛选指定日期范围内的订单后才尝试确定首单，这可能导致错误地将10月份内的非首单订单作为首单处理。
思路：
核心还是在于确定哪条记录是新用户的首单，只要这个明确了，只需要联立两个表根据公式求解即可。
看到很多题解使用rank窗口函数，我使用min窗口函数，求得每个用户的首次记录时间，异曲同工
