【字节跳动】统计 Pro 用户第一季度活跃积分
JOIN 连接聚合函数GROUP BYCASE WHEN用户行为分析面试真题
题目描述
来源：字节跳动 SQL 面试真题
某在线项目管理 SaaS 公司希望评估付费用户的产品使用深度和活跃度，识别高价值活跃用户，为后续服务和营销策略提供依据。
数据表
users 用户信息表
| 字段 | 类型 | 说明 |
|---|---|---|
| user_id | INT | 用户唯一编号，主键 |
| user_name | VARCHAR(50) | 用户名 |
| registration_date | DATE | 注册日期 |
| plan_type | VARCHAR(20) | 订阅计划，例如 Free、Pro、Enterprise |
user_events 用户事件表
| 字段 | 类型 | 说明 |
|---|---|---|
| event_id | INT | 事件唯一编号，主键 |
| user_id | INT | 执行事件的用户 ID |
| event_type | VARCHAR(50) | 事件类型 |
| event_timestamp | DATETIME | 事件发生时间 |
筛选条件
只统计同时满足以下条件的用户：
- registration_date 在 2025-01-01 至 2025-06-30 之间，包含首尾两日。
- plan_type = 'Pro'。
- 在 2025 年第一季度至少发生过一次 login 事件。
积分和事件数均只统计 2025 年第一季度，即 2025-01-01 00:00:00 至 2025-04-01 00:00:00 左闭右开范围内的事件。
积分规则
| event_type | 积分 |
|---|---|
| create_task | 5 |
| export_report | 10 |
| invite_member | 8 |
| 其他事件，包括 login | 1 |
输出要求
| 字段 | 说明 |
|---|---|
| user_profile | 用户名(用户ID)，例如 Alice(101) |
| total_activity_score | Q1 全部事件的总活跃积分 |
| avg_monthly_events | Q1 总事件数除以 3，四舍五入保留 2 位小数 |
排序规则：
- total_activity_score 降序。
- avg_monthly_events 降序。
- user_id 升序。
数据样例
| user_idPKINT | user_nameVARCHAR(50) | registration_dateDATE | plan_typeVARCHAR(20) |
|---|---|---|---|
| 101 | Alice | 2025-01-05 | Pro |
| 102 | Bob | 2025-02-10 | Pro |
| 103 | Carol | 2025-01-20 | Free |
| 104 | David | 2024-12-31 | Pro |
| 105 | Emma | 2025-03-01 | Enterprise |
| 106 | Frank | 2025-06-15 | Pro |
| 107 | Grace | 2025-03-20 | Pro |
| 108 | Henry | 2025-03-15 | Pro |
输入数据显示 8 / 9 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里