如何在SQL中筛选用户加入邮件列表后的活动数据？

要实现仅统计用户首次加入邮件列表之日起的活动数据（后续退出不影响），核心是先获取每个用户的首次加入日期，再过滤出该日期之后的用户活动记录，最后完成统计计算。

步骤说明 #

1. 获取用户首次加入日期：通过分组聚合，筛选每个用户mailing_list=1的最小日期，这就是用户首次加入邮件列表的时间节点。
2. 关联并过滤数据：将原表与首次加入日期的结果关联，只保留用户活动日期晚于或等于首次加入日期的记录。
3. 执行统计操作：在过滤后的数据集上完成你需要的计数、求和操作。

完整SQL代码 #

SELECT
  COUNT(DISTINCT user_id) AS user_count, -- 统计去重用户数，若原需求是统计记录数可去掉DISTINCT
  COUNT(DISTINCT date) AS active_date_count, -- 修正原sum(distinct date)的语法错误，统计活跃日期数
  SUM(characters_posted) AS total_characters_posted
FROM (
  SELECT
    d.date,
    d.user_id,
    d.session_id,
    d.characters_posted,
    d.variant_id,
    j.first_join_date
  FROM database_name d
  -- 关联用户首次加入邮件列表的日期
  LEFT JOIN (
    SELECT
      user_id,
      MIN(date) AS first_join_date
    FROM database_name
    WHERE mailing_list = 1
    GROUP BY user_id
  ) j ON d.user_id = j.user_id
  WHERE 
    d.date BETWEEN '2022-09-01' AND '2022-09-30' -- 修正9月无31号的日期范围错误
    AND j.first_join_date IS NOT NULL -- 仅统计有加入过邮件列表的用户
    AND d.date >= j.first_join_date -- 仅保留用户加入后的活动数据
) filtered_data

关键细节说明 #

• 原查询中sum(distinct date)属于语法逻辑错误（日期无法求和），这里改为count(distinct date)用于统计用户活跃的不同日期数，若你有其他需求可自行调整。
• 日期范围修正为2022-09-30，因为9月没有31号，原条件会导致数据遗漏或报错。
• 若需要包含从未加入过邮件列表的用户数据，可去掉j.first_join_date IS NOT NULL的过滤条件，但这类用户的first_join_date为NULL，不会被计入统计（因为d.date >= NULL结果为NULL，会被排除）。

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