MySql场景面试题：电商业务中用户的举动路径和转化率分析 - IT评测·应用市场-qidao123.com

WITH user_orders_2023 AS (
-- 获取2023年内的订单数据，并标记首次、第二次、最后一次购买日期
SELECT
u.user_id,
u.city,
u.register_date,
o.order_date,
o.amount,
MIN(o.order_date) OVER (PARTITION BY o.user_id) AS first_order_date,
LEAD(o.order_date, 1) OVER (PARTITION BY o.user_id ORDER BY o.order_date) AS second_order_date,
MAX(o.order_date) OVER (PARTITION BY o.user_id) AS last_order_date,
COUNT(o.order_id) OVER (PARTITION BY o.user_id) AS order_count,
SUM(o.amount) OVER (PARTITION BY o.user_id) AS total_amount
FROM
users u
JOIN
orders o ON u.user_id = o.user_id
WHERE
o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
),
user_behavior_path AS (
-- 统计用户行为路径
SELECT
user_id,
city,
first_order_date,
second_order_date,
last_order_date,
order_count,
total_amount
FROM
user_orders_2023
GROUP BY
user_id, city, first_order_date, second_order_date, last_order_date, order_count, total_amount
),
conversion_analysis AS (
-- 计算转化率
SELECT
city,
COUNT(DISTINCT CASE WHEN first_order_date IS NOT NULL THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS registration_to_first_purchase,
COUNT(DISTINCT CASE WHEN second_order_date IS NOT NULL THEN user_id END) * 1.0 / COUNT(DISTINCT CASE WHEN first_order_date IS NOT NULL THEN user_id END) AS first_to_second_purchase,
COUNT(DISTINCT CASE WHEN total_amount >= 10000 THEN user_id END) * 1.0 / COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) AS repurchase_to_high_value
FROM
user_orders_2023
GROUP BY
city
),
retention_analysis AS (
-- 计算用户留存率
SELECT
city,
COUNT(DISTINCT CASE WHEN DATEDIFF(order_date, first_order_date) >= 30 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS retention_30d,
COUNT(DISTINCT CASE WHEN DATEDIFF(order_date, first_order_date) >= 60 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS retention_60d,
COUNT(DISTINCT CASE WHEN DATEDIFF(order_date, first_order_date) >= 90 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS retention_90d
FROM
user_orders_2023
GROUP BY
city
)
-- 综合输出：按城市输出用户行为路径、转化率和留存率
SELECT
ubp.city,
AVG(DATEDIFF(second_order_date, first_order_date)) AS avg_days_first_to_second,
AVG(DATEDIFF(last_order_date, first_order_date)) AS avg_days_first_to_last,
AVG(order_count) AS avg_order_count,
AVG(total_amount) AS avg_total_amount,
ca.registration_to_first_purchase,
ca.first_to_second_purchase,
ca.repurchase_to_high_value,
ra.retention_30d,
ra.retention_60d,
ra.retention_90d
FROM
user_behavior_path ubp
JOIN
conversion_analysis ca ON ubp.city = ca.city
JOIN
retention_analysis ra ON ubp.city = ra.city
GROUP BY
ubp.city,
ca.registration_to_first_purchase,
ca.first_to_second_purchase,
ca.repurchase_to_high_value,
ra.retention_30d,
ra.retention_60d,
ra.retention_90d;

复制代码