题目

我们有两个表，一个是 notification_deliveries 表，另一个是包含 created 和购买 conversion dates 的 users 表。如果用户没有购买，那么 conversion_date 列为 NULL。

编写一个查询，以获取用户转换前的推送通知总数的分布情况。

示例：

输入：

notification_deliveries 表

列名	类型
notification	VARCHAR
user_id	INTEGER
created_at	DATETIME

users 表

列名	类型
id	INTEGER
created_at	DATETIME
conversion_date	DATETIME

输出：

列名	类型
total_pushes	INTEGER
frequency	INTEGER

答案

解题思路

如果查看用户转换前收到的推送通知总数的分布情况，最终的结果应该看起来像下面这样：

total_pushes | frequency
-------------+----------
    0        |  100
    1        |  250
    2        |  300
   ...       |  ...

购买前收到消息次数为0的用户人数有100个， 购买前接受了1次消息的有250人，接收了2次消息的用户有300人……

我们需要获取用户转换前推送通知的总数分布，可以考虑以下几点：

1. 在两个表之间根据 user_id 字段进行连接。
2. 排除所有未转换的用户。
3. 将 conversion_date 设置为大于 notification_deliveries 表中的 created_at 值，以获取发送给用户的所有通知。

我们知道这是一个 LEFT JOIN，以确保包含那些在没有任何推送通知的情况下转换的用户。

我们可以先计算每个用户的通知数量，然后按该数量分组，以获取总体分布。

答案代码

下面是实现该需求的SQL查询：

SELECT total_pushes, COUNT(*) AS frequency
FROM (
    SELECT u.id, COUNT(nd.notification) as total_pushes
    FROM users AS u
    LEFT JOIN notification_deliveries AS nd
        ON u.id = nd.user_id
            AND u.conversion_date >= nd.created_at
    WHERE u.conversion_date IS NOT NULL
    GROUP BY 1
) AS pushes
GROUP BY 1

• LEFT JOIN: 确保即使用户没有收到任何推送通知也能被包含在内。
• u.conversion_date >= nd.created_at: 确保只计算在用户转换之前发送的通知。
• COUNT(nd.notification): 计算每个用户在转换前收到的通知数量。
• GROUP BY: 对每个用户计算的通知数量进行分组，然后对这些分组进行频率统计。

通过以上查询，可以得到每个用户在转换前收到的推送通知总数的分布情况。

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