SQL获取新用户-解网

问：

我正在为我的系统创建新的用户获取报告，专门针对以前从未登录过的用户。下面的查询成功地涵盖了 4 天的时间段（从到），但我希望将其扩展到 30 天的时间范围。2023-11-132023-11-16

SELECT '2023-11-13' AS event_date, COUNT(DISTINCT USER_ID) AS new_user_acquisition
FROM EVENTS
WHERE EVENT_DATE = '2023-11-13'

UNION ALL

SELECT '2023-11-14' AS event_date, COUNT(DISTINCT USER_ID) AS new_user_acquisition
FROM EVENTS
WHERE EVENT_DATE = '2023-11-14'
  AND USER_ID NOT IN (
    SELECT DISTINCT USER_ID
    FROM EVENTS
    WHERE EVENT_DATE = '2023-11-13'
  )

UNION ALL

SELECT '2023-11-15' AS event_date, COUNT(DISTINCT USER_ID) AS new_user_acquisition
FROM EVENTS
WHERE EVENT_DATE = '2023-11-15'
  AND USER_ID NOT IN (
    SELECT DISTINCT USER_ID
    FROM EVENTS
    WHERE EVENT_DATE IN ('2023-11-13', '2023-11-14')
  )

UNION ALL

SELECT '2023-11-16' AS event_date, COUNT(DISTINCT USER_ID) AS new_user_acquisition
FROM EVENTS
WHERE EVENT_DATE = '2023-11-16'
  AND USER_ID NOT IN (
    SELECT DISTINCT USER_ID
    FROM EVENTS
    WHERE EVENT_DATE IN ('2023-11-13', '2023-11-14', '2023-11-15')
  );

有没有更有效的方法来构建我的查询，而无需单独列出每一天并排除其他日期？

我想避免手动指定每个日期的繁琐过程，同时确保报告准确捕获以前未登录过的新用户。

SQL格式

WITH UserLoginStatus AS (
  SELECT
    USER_ID,
    EVENT_DATE,
    LAG(EVENT_DATE) OVER (PARTITION BY USER_ID ORDER BY EVENT_DATE) AS previous_login_date
  FROM EVENTS
  WHERE EVENT_DATE BETWEEN '2023-10-15' AND '2023-11-15'
)

SELECT
  EVENT_DATE,
  COUNT(DISTINCT USER_ID) AS new_user_acquisition
FROM UserLoginStatus
WHERE previous_login_date IS NULL
GROUP BY EVENT_DATE
ORDER BY EVENT_DATE;

0赞 JNevill 11/17/2023 #2

您可以将整个问题简化为几个聚合。首先确定您首次看到每个日期的日期，然后计算每个日期的共享数：user_idmin(event_date)user_id

SELECT first_date, COUNT(user_id)
FROM 
  (
     SELECT user_id, MIN(event_date) as first_date
     FROM EVENT_DATE
     GROUP BY user_id
  ) dt
GROUP BY first_date

这将为您提供在表中找到新用户的每个日期的新用户计数。您可以在该子查询上抛出一个子句，以将其限制为最近 30 天，例如或适合您的 RDBMS 的任何语法。WHEREWHERE event_date >= CURRENT_DATE - INTERVAL '30' DAY

这是它的实际操作。

0赞 Trijit 11/17/2023 #3

我相信这应该这样做

WITH mydata
AS (
    SELECT min(event_date) OVER (PARTITION BY user_id) first_date
        ,user_id
        ,event_date
    FROM EVENTS
    WHERE event_date BETWEEN '2023-11-13'
            AND DATEADD(day, 30, '2023-11-13')
    )
SELECT count(DISTINCT user_id)
    ,event_date
FROM mydata
WHERE first_date = event_date
group by event_date

上一个：无法查询外部表 - 批量加载数据转换错误

下一个：Python 中的数据库 SQL [已关闭]

SQL获取新用户

SQL To Get New User Acquisition

评论