snowflake-schema - 一个月内重新激活
问题描述
我需要找到重新激活一个月的用户。之前开票、然后停止开票并在当月再次开始开票的用户数。下面的查询给了我一个月的期望结果。
SELECT COUNT(DISTINCT USERID)
FROM (
SELECT USERID
,MIN(DATE ( INVOICE_DT)) AS ADD_ON_GA_DATE
FROM INVOICE_D AS ID
WHERE
USERID IN (
(
SELECT USERID
FROM INVOICE_D AS ID
WHERE INVOICE_AMT > 0
AND LAST_DAY(INVOICE_DT) = ADD_MONTHS(LAST_DAY(CURRENT_DATE - 1), - 1)
GROUP BY USERID
)
MINUS
(SELECT USERID
FROM INVOICE_D AS ID
WHERE LAST_DAY(INVOICE_DT) = ADD_MONTHS(LAST_DAY(CURRENT_DATE - 1), - 2)
GROUP BY USERID)
)
GROUP BY USERID
) AS TOTAL_POP
WHERE LAST_DAY(ADD_ON_GA_DATE) != ADD_MONTHS(LAST_DAY(CURRENT_DATE - 1), - 1)
;
我希望此查询计算除当前月份以外的所有月份(不包括 2020 年 9 月)。这给了我所有月份的数据,其中用户在当月活跃,而不是在上个月活跃。现在,从这个列表中,我只希望那些在所有较早月份以及当前月份都活跃的用户。不确定我需要在哪里添加过滤器以排除当前月份。
WITH MONTH_SUMMARY AS
(
SELECT USERID
,TO_CHAR(INVOICE_DT,'YYYY-MM') "INVOICE_MONTH"
,TO_CHAR(ADD_MONTHS(INVOICE_DT,-1),'YYYY-MM') "PREV_MONTH"
,SUM(INVOICE_AMT) "MONTHLY_TOTAL"
FROM INVOICE_DATA
WHERE INVOICE_DT >= TRUNC(ADD_MONTHS(current_date(),-13),'MONTH')
GROUP BY 1,2,3
),
USER_DATA AS
(
SELECT USERID, INVOICE_MONTH, MONTHLY_TOTAL
FROM MONTH_SUMMARY MS_THIS
WHERE NOT EXISTS
(
SELECT USERID
FROM MONTH_SUMMARY MS_NEXT
WHERE
MS_THIS.USERID = MS_NEXT.USERID AND
MS_THIS.NEXT_MONTH = MS_NEXT.INVOICE_MONTH
)
AND MS_THIS.INVOICE_MONTH < TO_CHAR(current_date(),'YYYY-MM')
)
SELECT INVOICE_MONTH, COUNT(DISTINCT USERID) "USER_COUNT"
FROM USER_DATA
GROUP BY INVOICE_MONTH
ORDER BY INVOICE_MONTH
;
解决方案
这个 SQL 可能会让你接近你想要的:
-- Create a CTE that holds monthly summaries per user; adjust as necessary to hold the data required in the final output
-- Last day of month used, rather than just the month, to make subsequent date logic easier
WITH MONTHLY_SUMMARY AS (
SELECT USERID,
LAST_DAY(INVOICE_DT) AS INVOICE_MONTH,
SUM(INVOICE_AMT) MONTHLY_AMT
FROM INVOICE_D
GROUP BY 1,2
HAVING MONTHLY_AMT > 0 -- Only include user/month combinations that have an invoice amount. Only required if zero-value invoices exists. Would need adjusting if +ve and -ve amounts exist that could sum to zero
)
--Main select
SELECT THIS_MONTH.INVOICE_MONTH, THIS_MONTH.USERID, THIS_MONTH.MONTHLY_AMT
FROM MONTHLY_SUMMARY THIS_MONTH
WHERE NOT EXISTS ( -- exclude records where there is an invoice amount for the same user in the previous month
SELECT 1 FROM MONTHLY_SUMMARY LAST_MONTH
WHERE THIS_MONTH.USERID = LAST_MONTH.USERID
AND ADD_MONTHS(THIS_MONTH.INVOICE_MONTH,-1) = LAST_MONTH.INVOICE_MONTH
)
AND EXISTS ( -- include records where there is an invoice amount for the same user in month 2, or more, priior to this month
SELECT 1 FROM MONTHLY_SUMMARY LAST_MONTH
WHERE THIS_MONTH.USERID = LAST_MONTH.USERID
AND LAST_MONTH.INVOICE_MONTH <= ADD_MONTHS(THIS_MONTH.INVOICE_MONTH,-2)
)
AND LAST_DAY(CURRENT_DATE) > THIS_MONTH.INVOICE_MONTH -- EXCLUDE THE CURRENT MONTH
AND THIS_MONTH.INVOICE_MONTH <> (SELECT MIN(INVOICE_MONTH) FROM MONTHLY_SUMMARY) -- Exclude 1st month in dataset
ORDER BY THIS_MONTH.INVOICE_MONTH, THIS_MONTH.USERID
;
推荐阅读
- r - 如何在 R 中重复
- node.js - 来自 lambda (nodejs) 的 EC2 用户数据中的问题设置变量
- vue.js - Vue中的NavBar使用v-for和路由器
- javascript - Elementor 表格的字数计数器
- python - 基于关系过滤记录 | 姜戈
- python - pywinauto 挂在带有 448 个条目的列表的应用程序上
- ms-access - 有没有办法在 MS Access 中加入表以获得此输出?
- mfc - MFC获取鼠标指针坐标问题
- localhost - 如何从另一台计算机连接到 localhost:5555?
- c++ - 如何将main Dlg的指针从线程传递到MFC内部的函数