ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySql场景口试题：电商业务中高净值用户行为分析 [打印本页]

作者: 不到断气不罢休 时间: 7 天前
标题: MySql场景口试题：电商业务中高净值用户行为分析
❃博主首页：   「码到三十五」   ，同名公众号 :「码到三十五」，wx号 : 「liwu0213」
  ☠博主专栏：   <mysql高手>    <elasticsearch高手>    <源码解读>    <java核心>    <口试攻关>
  ♝博主的话：  搬的每块砖，皆为峰峦之基；公众号搜刮「码到三十五」关注这个爱发技能干货的coder，一起筑基

电商业务,一个订单表 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;

复制代码

表明：

high_value_users: 找出在2023年内累计购买金额超过10,000元的用户，并计算他们的总购买金额和订单数量。
total_users: 计算总用户数。
city_stats: 按都会分组，计算每个都会的高代价用户数量及其在总用户中的占比。
最终查询: 将高代价用户的信息与都会统计信息联合，计算平均购买金额、平均购买次数，并输出每个高代价用户的具体信息及其所在都会的高代价用户统计。

关注公众号[码到三十五]获取更多技能干货 !

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)