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

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

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

表结构：

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

需求：

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

参考SQL：

WITH high_value_users AS (
    SELECT
        u.user_id,
        ,
        SUM(o.amount) AS total_amount,
        COUT(_id) AS total_orders
    FROM
        users u
    JOI
        orders o O u.user_id = o.user_id
    WHERE
        _date BETWEE '202-01-01' AD '202-12-1'
    GROUP BY
        u.user_id, 
    HAVIG
        SUM(o.amount) > 10000
),
total_users AS (
    SELECT
        COUT(DISTICT user_id) AS total_user_count
    FROM
        users
),
city_stats AS (
    SELECT
        city,
        COUT(user_id) AS high_value_user_count,
        COUT(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,
    hv,
    _amount,
    _orders,
    AVG(_amount) OVER () AS avg_amount,
    AVG(_orders) OVER () AS avg_orders,
    cs.high_value_user_count,
    cs.high_value_user_ratio
FROM
    high_value_users hvu
JOI
    city_stats cs O hv = ;

解释：

1. high_value_users: 出在202年内累计购买金额超过10,000元的用户，并计算他们的总购买金额和订单数量。
2. total_users: 计算总用户数。
3. city_stats: 按城市分组，计算每个城市的高价值用户数量及其在总用户中的占比。
4. 最终查询: 将高价值用户的信息与城市统计信息结合，计算平均购买金额、平均购买次数，并输出每个高价值用户的详细信息及其所在城市的高价值用户统计。