MySql场景面试题：电商业务中高净值用户行为分析

公众号:码到三十五

发布于 2025-01-22 09:51:50

10700

代码可运行

文章被收录于专栏：设计模式设计模式

运行总次数：0

代码可运行

电商业务,一个订单表 orders 和一个用户表 users。你需要分析用户的购买行为，特别是那些在特定时间段内购买金额超过一定阈值的用户，并计算他们的平均购买金额、购买次数以及他们在总用户中的占比。

表结构：

用户表 (users):
- user_id (用户ID, 主键)
- register_date (注册日期)
- city (所在城市)
订单表 (orders):
- order_id (订单ID, 主键)
- user_id (用户ID, 外键)
- order_date (订单日期)
- amount (订单金额)

需求：

找出在2023年1月1日至2023年12月31日期间，累计购买金额超过10,000元的用户。
计算这些用户的平均购买金额、平均购买次数。
计算这些高价值用户在总用户中的占比。
按城市分组，统计每个城市的高价值用户数量及其占比。

参考SQL：

WITH high_value_users AS (
    SELECT
        u.user_id,
        u.city,
        SUM(o.amount) AS total_amount,
        COUNT(o.order_id) AS total_orders
    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'
    GROUP BY
        u.user_id, u.city
    HAVING
        SUM(o.amount) > 10000
),
total_users AS (
    SELECT
        COUNT(DISTINCT user_id) AS total_user_count
    FROM
        users
),
city_stats AS (
    SELECT
        city,
        COUNT(user_id) AS high_value_user_count,
        COUNT(user_id) * 1.0 / (SELECT total_user_count FROM total_users) AS high_value_user_ratio
    FROM
        high_value_users
    GROUP BY
        city
)
SELECT
    hvu.user_id,
    hvu.city,
    hvu.total_amount,
    hvu.total_orders,
    AVG(hvu.total_amount) OVER () AS avg_amount,
    AVG(hvu.total_orders) OVER () AS avg_orders,
    cs.high_value_user_count,
    cs.high_value_user_ratio
FROM
    high_value_users hvu
JOIN
    city_stats cs ON hvu.city = cs.city;

解释：