【数据分析面试】27. 计算广告评论比例 （SQL）

题目: 计算广告评论比例

假设你有一个ads表，包含ID和广告名称，比如“劳动节衬衫促销”。feed_comments表保存了不同用户在常规信息流中对广告的评论。moments_comments表保存了不同用户在moments中对广告的评论。

编写一个查询，获取广告在feed和moments中的评论比例。
示例：
输入：

feed_comments表

moments_comments表

ads表

输出:

答案

对于每个广告，我们想要的是来自feed和moments的评论比例：

% feeds = feed的评论数 / (feed的评论数 + moments的评论数)
% moments = moments的评论数 / (feed的评论数 + moments的评论数)

先看看feed_comments表。想要求每个ad_id的总评论数，我们可以简单地使用GROUP BY来计数。

SELECT 
    ad_id
    , COUNT(DISTINCT comment_id) AS num_comments
FROM feed_comments AS fc 
GROUP BY 1

这里还需要注意到一个细节：如果一个广告在feed_comments表中不存在，但存在于ads表或moments_comments表中，上面的方法就会将该广告过滤掉，使其评论计数为零。

我们可以通过将表左连接到来解决这个问题，同样moments_comments表也需要执行相同的操作。

WITH fc AS (
    SELECT 
        ads.id AS ad_id
        , COUNT(DISTINCT comment_id) AS num_comments
    FROM ads
    LEFT JOIN feed_comments AS fc 
        ON ads.id = fc.ad_id
    GROUP BY 1
),
mc AS (
    SELECT 
        ads.id AS ad_id
        , COUNT(DISTINCT comment_id) AS num_comments
    FROM ads
    LEFT JOIN moments_comments AS mc 
        ON ads.id = mc.ad_id
    GROUP BY 1
)
SELECT * FROM fc,mc

现在给定这两个计数，我们可以将它们与广告表连接起来，获取每个广告的名称，并计算总比例的方程式。

WITH fc AS (
    SELECT 
        ads.id AS ad_id
        , COUNT(DISTINCT comment_id) AS num_comments
    FROM ads
    LEFT JOIN feed_comments AS fc 
        ON ads.id = fc.ad_id
    GROUP BY 1
),
mc AS (
    SELECT 
        ads.id AS ad_id
        , COUNT(DISTINCT comment_id) AS num_comments
    FROM ads
    LEFT JOIN moments_comments AS mc 
        ON ads.id = mc.ad_id
    GROUP BY 1
)
SELECT ads.name
    , fc.num_comments/(fc.num_comments + mc.num_comments) AS percentage_feed
    , mc.num_comments/(fc.num_comments + mc.num_comments) AS percentage_moments
FROM ads
LEFT JOIN fc 
    ON ads.id = fc.ad_id 
LEFT JOIN mc 
    ON ads.id = mc.ad_id

更多详细答案可关注公众号查阅。