提问人：Rahul Nakod 提问时间：10/15/2023 最后编辑：Dale KRahul Nakod 更新时间：10/15/2023 访问量：157
编写 SQL 查询，计算销售漏斗各个阶段的用户转化率
Write an SQL query to calculate the conversion rate of users from each stage of the sales funnel
问：
考虑一下，我有一个表“Sales”，其中包含列“user_id”、“product_id”、“timestamp”和“purchase_status”。如何编写 SQL 查询来计算销售漏斗每个阶段（例如，查看产品、添加到购物车、完成购买）的用户转化率。
例：
| user_id | product_id | 时间戳 | purchase_status |
|---|---|---|---|
| 1 | 一个 | 2023-07-25 08:15:00 | 看 |
| 2 | B | 2023-07-25 09:30:00 | added_to_cart |
| 1 | 一个 | 2023-07-25 10:00:00 | 完成 |
| 3 | C | 2023-07-25 12:45:00 | 看 |
| 2 | B | 2023-07-25 14:20:00 | 完成 |
期望的结果：
| purchase_status | viewed_users | added_to_cart_users | completed_users | total_users |
|---|---|---|---|---|
| 看 | 2 | 0 | 0 | 2 |
| Added_to_cart | 0 | 1 | 0 | 1 |
| 完成 | 1 | 0 | 2 | 3 |
我尝试编写如下查询：
SELECT
purchase_status,
SUM(CASE WHEN purchase_status = 'viewed' THEN 1 ELSE 0 END) AS viewed_users,
SUM(CASE WHEN purchase_status = 'added_to_cart' THEN 1 ELSE 0 END) AS added_to_cart_users,
SUM(CASE WHEN purchase_status = 'completed' THEN 1 ELSE 0 END) AS completed_users,
COUNT(*) AS total_users
FROM Sales
GROUP BY purchase_status
ORDER BY
CASE
WHEN purchase_status = 'viewed' THEN 1
WHEN purchase_status = 'added_to_cart' THEN 2
WHEN purchase_status = 'completed' THEN 3
END;
但是给出如下结果是错误的：
| purchase_status | viewed_users | added_to_cart_users | completed_users | total_users |
|---|---|---|---|---|
| 看 | 2 | 0 | 0 | 2 |
| added_to_cart | 0 | 1 | 0 | 1 |
| 完成 | 0 | 0 | 2 | 2 |
谁能帮帮我？
答：
1赞
MatBailie
10/15/2023
#1
一种有点幼稚的方法是首先“按用户”透视数据，然后进行聚合。
这会计算事件而不是用户。这意味着，用户两次查看同一产品（甚至两次购买同一产品，相隔几个月）可能会扭曲结果。这取决于你想要什么。
WITH
user_summary AS
(
SELECT
user_id,
product_id,
purchase_status,
SUM(CASE WHEN purchase_status = 'viewed' THEN 1 ELSE 0 END)
OVER (PARTITION BY user_id, product_id)
AS viewed_users,
SUM(CASE WHEN purchase_status = 'added_to_cart' THEN 1 ELSE 0 END)
OVER (PARTITION BY user_id, product_id)
AS added_to_cart_users,
SUM(CASE WHEN purchase_status = 'completed' THEN 1 ELSE 0 END)
OVER (PARTITION BY user_id, product_id)
AS completed users,
COUNT(*)
OVER (PARTITION BY user_id, product_id)
AS total_users
FROM
Sales
)
SELECT
purchase_status,
SUM(viewed_users),
SUM(added_to_cart_users),
SUM(completed_users),
SUM(total_users)
FROM
user_summary
GROUP BY
purchase_status
ORDER BY
CASE
WHEN purchase_status = 'viewed' THEN 1
WHEN purchase_status = 'added_to_cart' THEN 2
WHEN purchase_status = 'completed' THEN 3
END;
评论