【字节跳动】统计粉丝内容行为整体 CTR
JOIN 连接聚合函数用户行为分析漏斗分析面试真题
题目描述
来源：字节跳动 SQL 面试真题
某内容平台拥有创作者与粉丝关系、创作者与内容关系，以及用户在内容上的行为明细。现在需要统计所有有效粉丝行为的整体点击率（CTR）。
数据表
a 创作者和粉丝关系表
| 字段 | 类型 | 说明 |
|---|---|---|
| author_id | BIGINT | 创作者 ID |
| fans_id | BIGINT | 粉丝 ID |
| create_date | DATE | 建立关注关系日期 |
b 创作者和内容关系表
| 字段 | 类型 | 说明 |
|---|---|---|
| author_id | BIGINT | 创作者 ID |
| content_id | BIGINT | 内容 ID |
c 用户内容行为明细表
| 字段 | 类型 | 说明 |
|---|---|---|
| content_id | BIGINT | 内容 ID |
| fans_id | BIGINT | 产生行为的用户 ID |
| show_num | INT | 曝光次数 |
| read_num | INT | 阅读次数 |
| like_num | INT | 点赞次数 |
| comment_num | INT | 评论次数 |
题目要求
- CTR = 作者对应粉丝的总阅读次数 / 作者对应粉丝的总曝光次数。
- 只有行为用户是该内容作者的粉丝时，该行为才计入统计。
- 非作者粉丝产生的阅读和曝光不能计入。
- 统计全体有效粉丝行为的整体 CTR，而不是先计算各作者 CTR 再取平均。
- CTR 四舍五入保留 4 位小数。
- create_date 不参与过滤。
期望输出列
| 列名 | 说明 |
|---|---|
| fans_ctr | 全体有效粉丝行为的整体 CTR |
数据样例
| author_idPKBIGINT | fans_idPKBIGINT | create_dateDATE |
|---|---|---|
| 332579 | 985035 | 2022-01-01 |
| 332579 | 849602 | 2022-01-15 |
| 332579 | 952566 | 2022-03-20 |
| 970382 | 930554 | 2022-06-01 |
| 970382 | 985035 | 2022-09-23 |
| 960725 | 590742 | 2022-11-10 |
| 960725 | 985035 | 2022-12-06 |
| 960725 | 940672 | 2023-01-12 |
输入数据显示 8 / 9 行
SQL 编辑器正在保存草稿...
Ctrl + Enter 运行
正在加载 SQL 编辑器...
运行你的 SQL 查询后，结果将显示在这里