校招算法笔面试 | SQL笔试面试编程题-请写出计算粉丝ctr的sql语句

题目## 题目

题目链接

这道题目要求我们计算粉丝的点击率（CTR），我们要做的事情如下：

1. 确定总体问题

我们需要计算粉丝的CTR，即粉丝对内容的总阅读次数与总曝光次数的比值，公式为：CTR = 总阅读次数 / 总曝光次数。

2. 分析关键问题

• 连接表：将三个表连接起来，以便获取每个粉丝对内容的行为数据。
• 计算总阅读次数和总曝光次数：对每个粉丝的阅读次数和曝光次数进行汇总。
• 计算CTR：计算总阅读次数与总曝光次数的比值。

3. 解决每个关键问题的代码及讲解

步骤1：连接表

我们需要从 c 表中提取每个粉丝的总曝光次数和总阅读次数。
通过 content_id 关联 b 表，获取对应的 author_id。
通过 author_id 和 fans_id 关联 a 表，确保粉丝和创作者之间存在关系，因此我们使用JOIN将三个表连接起来：

from
    a
    join b on a.author_id = b.author_id
    join c on b.content_id = c.content_id

• JOIN b ON a.author_id = b.author_id：通过创作者ID连接a和b表，以便获取创作者与内容的关系。
• JOIN c ON b.content_id = c.content_id：通过内容ID连接b和c表，以便获取内容的行为数据。

步骤2：计算总阅读次数和总曝光次数

我们使用SUM函数对每个粉丝的阅读次数和曝光次数进行汇总：

select
    sum(c.read_num) / sum(c.show_num) as fans_ctr

• SUM(c.read_num)：计算粉丝对内容的总阅读次数。
• SUM(c.show_num)：计算粉丝对内容的总曝光次数。

步骤3：计算CTR

我们计算总阅读次数与总曝光次数的比值：

sum(c.read_num) / sum(c.show_num) as fans_ctr

• sum(c.read_num) / sum(c.show_num)：CTR = 总阅读次数 / 总曝光次数，计算CTR。

步骤4：过滤数据

我们使用WHERE子句确保粉丝ID匹配：

where
    a.fans_id = c.fans_id

• WHERE a.fans_id = c.fans_id：确保粉丝ID在a和c表中匹配。

完整代码

select
    sum(c.read_num) / sum(c.show_num) as fans_ctr
from
    a
    join b on a.author_id = b.author_id
    join c on b.content_id = c.content_id
where
    a.fans_id = c.fans_id

题目链接

题目分析

我们需要从 user_profile 表中选择 gender 为“male”的记录，并计算这些记录的数量和 GPA 的平均值。

SQL 查询

以下是实现上述思路的 SQL 查询：

SELECT COUNT(*) AS male_num, ROUND(AVG(gpa), 1) AS avg_gpa
FROM user_profile
WHERE gender = 'male';

解释

• SELECT COUNT(*) AS male_num: 计算 gender 为“male”的记录数量，并将结果命名为 male_num。
• ROUND(AVG(gpa), 1) AS avg_gpa: 计算 gpa 的平均值，并使用 ROUND() 函数保留到小数点后1位，结果命名为 avg_gpa。
• FROM user_profile: 从 user_profile 表中获取数据。
• WHERE gender = 'male': 只返回 gender 为“male”的记录。