SQL计算F2功能用户30天内升级Premium转化率结果异常排查

需求说明 #

计算使用过功能F2（events表中type为F2）且在注册首30天内升级为Premium的用户占比，结果保留两位小数，预期值为0.33。

数据表结构 #

users表 #

user_id name    join_date
1       Jon     2020-02-14
2       Jane    2020-02-14
3       Jill    2020-02-15
4       Josh    2020-02-15
5       Jean    2020-02-16
6       Justin  2020-02-17
7       Jeremy  2020-02-18

events表 #

user_id type access_date
1       F1   2020-03-01
2       F2   2020-03-02
2       P    2020-03-12
3       F2   2020-03-15
4       F2   2020-03-15
1       P    2020-03-16
3       P    2020-03-22

尝试的SQL代码 #

;WITH users AS (
  SELECT * FROM (
    VALUES
        (1, 'Jon', CAST('14-02-20' AS date)), 
        (2, 'Jane', CAST('14-02-20' AS date)), 
        (3, 'Jill', CAST('15-02-20' AS date)), 
        (4, 'Josh', CAST('15-02-20' AS date)), 
        (5, 'Jean', CAST('16-02-20' AS date)), 
        (6, 'Justin', CAST('17-02-20' AS date)),
        (7, 'Jeremy', CAST('18-02-20' AS date))
  ) AS _ (user_id, name, join_date)
),
events AS (
  SELECT * FROM (
    VALUES
        (1, 'F1', CAST('01-03-20' AS date)),
        (2, 'F2', CAST('02-03-20' AS date)), 
        (2, 'P', CAST('12-03-20' AS date)),
        (3, 'F2', CAST('15-03-20' AS date)), 
        (4, 'F2', CAST('15-03-20' AS date)), 
        (1, 'P', CAST('16-03-20' AS date)), 
        (3, 'P', CAST('22-03-20' AS date))
  ) AS _ (user_id, type, access_date)
),
feature_two_upg AS (
    SELECT * 
    FROM events
    WHERE type = 'F2'
),
premium_upg AS (
    SELECT *
    FROM events
    WHERE type = 'P'
),
differ_date AS (
    SELECT feature.user_id, premium.access_date
    FROM feature_two_upg AS feature
    INNER JOIN premium_upg AS premium
    ON feature.user_id = premium.user_id
    WHERE DATEDIFF(DAY, feature.access_date, premium.access_date) < 30
)

SELECT ROUND(AVG(CAST(CASE WHEN differ_date.user_id IS NOT NULL THEN 1.0 ELSE 0.0 END AS float)), 2) AS upgrade_rate
FROM users
LEFT JOIN differ_date
ON users.user_id = differ_date.user_id

当前问题 #

上述代码返回的upgrade_rate为0.29，与预期值0.33不符，需排查错误原因。

错误原因分析 #

• 分母范围错误：需求是计算使用过F2的用户中符合条件的占比，但代码用了所有7个用户作为分母。实际使用过F2的用户是user2、user3、user4，共3人，其中符合首30天升级的是user2、user3，共2人，2/3≈0.33才是正确结果，代码计算的是2/7≈0.29，所以结果错误。
• 时间判断逻辑错误：需求是注册首30天内升级，即升级日期access_date与注册日期join_date的间隔≤30天，但代码中用的是F2使用日期和升级日期的间隔，不符合需求逻辑。

修正后的SQL代码 #

;WITH users AS (
  SELECT * FROM (
    VALUES
        (1, 'Jon', CAST('14-02-20' AS date)), 
        (2, 'Jane', CAST('14-02-20' AS date)), 
        (3, 'Jill', CAST('15-02-20' AS date)), 
        (4, 'Josh', CAST('15-02-20' AS date)), 
        (5, 'Jean', CAST('16-02-20' AS date)), 
        (6, 'Justin', CAST('17-02-20' AS date)),
        (7, 'Jeremy', CAST('18-02-20' AS date))
  ) AS _ (user_id, name, join_date)
),
events AS (
  SELECT * FROM (
    VALUES
        (1, 'F1', CAST('01-03-20' AS date)),
        (2, 'F2', CAST('02-03-20' AS date)), 
        (2, 'P', CAST('12-03-20' AS date)),
        (3, 'F2', CAST('15-03-20' AS date)), 
        (4, 'F2', CAST('15-03-20' AS date)), 
        (1, 'P', CAST('16-03-20' AS date)), 
        (3, 'P', CAST('22-03-20' AS date))
  ) AS _ (user_id, type, access_date)
)
SELECT 
  ROUND(
    AVG(CASE WHEN p.access_date IS NOT NULL THEN 1.0 ELSE 0.0 END),
    2
  ) AS upgrade_rate
FROM (
  -- 先筛选出使用过F2的用户
  SELECT DISTINCT user_id, join_date
  FROM users u
  JOIN events e ON u.user_id = e.user_id
  WHERE e.type = 'F2'
) f2_users
LEFT JOIN (
  -- 筛选出注册首30天内升级的用户
  SELECT user_id, access_date
  FROM users u
  JOIN events e ON u.user_id = e.user_id
  WHERE e.type = 'P'
    AND DATEDIFF(DAY, u.join_date, e.access_date) <= 30
) p ON f2_users.user_id = p.user_id

验证结果 #

修正后的代码会统计使用过F2的3个用户中，有2个在注册首30天内升级，2/3≈0.33，符合预期值。

内容的提问来源于stack exchange，提问作者UntitledUserForEach