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

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

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

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

### 问题分解：

• 统计用户首单信息（生成子表t_first_order）：
• 定义按用户分区按行为时间排序的窗口：WINDOW wd_uid_first as (partition by uid order by event_time)
• 获取窗口内第一个行为时间：FIRST_VALUE(event_time) over(wd_uid_first) as event_time
• 获取窗口内第一个订单ID：FIRST_VALUE(order_id) over(wd_uid_first) as order_id
• 获取窗口内第一个订单金额：FIRST_VALUE(total_amount) over(wd_uid_first) as total_amount
• 统计每个订单原始金额（生成子表t_raw_amount）：
• 按订单号分组：GROUP BY order_id
• 商品价格求和：SUM(price * cnt) as raw_amount
• 关联用户首单的原始价格：t_first_order JOIN t_raw_amount USING(order_id)
• 筛选时间窗：WHERE DATE_FORMAT(event_time, "%Y-%m") = '2021-10'
• 基于以上结果统计首单平均交易金额和平均获客成本：
• 首单平均交易金额：AVG(total_amount) as avg_amount
• 平均获客成本：AVG(raw_amount-total_amount) as avg_cost

• 表头重命名：as

### 完整代码：

``````SELECT ROUND(AVG(total_amount), 1) as avg_amount,
ROUND(AVG(raw_amount-total_amount), 1) as avg_cost
FROM (
SELECT uid, total_amount, raw_amount
FROM (
SELECT DISTINCT uid,
FIRST_VALUE(event_time) over(wd_uid_first) as event_time,
FIRST_VALUE(order_id) over(wd_uid_first) as order_id,
FIRST_VALUE(total_amount) over(wd_uid_first) as total_amount
FROM tb_order_overall
WINDOW wd_uid_first as (partition by uid order by event_time)
) as t_first_order
JOIN (
SELECT order_id, SUM(price * cnt) as raw_amount
FROM tb_order_detail
GROUP BY order_id
) as t_raw_amount
USING(order_id)
WHERE DATE_FORMAT(event_time, "%Y-%m") = '2021-10'
) as t_first_order_info;
``````
SQL大厂真题 文章被收录于专栏

12 2 评论