I need to write a query that gives me the count with the following logic. The example below shows that ACCOUNT_ID
123 signup in 2020-02-21
so M0
is 1 and then the same ACCOUNT_ID
had an event in the consecutive month so M1
is 1.
M0 is a the signup date
M1 is signup date + 1 month
M2 is signup date + 2 consecutive months
M3 is signup date + 3 consecutive months
WITH M_O AS (
SELECT
parsed_data."ACCOUNT_ID" AS "parsed_data.account_id",
MIN(TO_CHAR(TO_DATE(parsed_data."TIMESTAMP"::timestamp_ntz ), 'YYYY-MM-DD')) AS "SIGNUP",
COUNT(DISTINCT (parsed_data."ACCOUNT_ID") ) AS "COUNT_USERS_O"
FROM "PUBLIC"."PARSED_DATA"
AS parsed_data
WHERE (parsed_data."ACCOUNT_ID") IS NOT NULL
AND (((parsed_data."EVENT") = 'Started'))
AND (
((TO_CHAR(TO_DATE(parsed_data."TIMESTAMP"::timestamp_ntz ), 'YYYY-MM-DD')) >= '2020-02-21')
AND ((parsed_data."TIMESTAMP"::timestamp_ntz ) < CURRENT_DATE())
)
GROUP BY 1),
M_1 AS (
SELECT
parsed_data."ACCOUNT_ID" AS "parsed_data.account_id",
TO_CHAR(TO_DATE(parsed_data."TIMESTAMP"::timestamp_ntz ), 'YYYY-MM-DD') AS "parsed_data.timestamp_date",
COUNT(DISTINCT (parsed_data."ACCOUNT_ID") ) AS "COUNT_USERS_1"
FROM "PUBLIC"."PARSED_DATA"
AS parsed_data INNER JOIN M_O ON parsed_data.account_id = M_O."parsed_data.account_id"
WHERE
(parsed_data."ACCOUNT_ID") IS NOT NULL
AND (((parsed_data."EVENT") = 'Started'))
AND (
(TO_CHAR(TO_DATE(parsed_data."TIMESTAMP"::timestamp_ntz ), 'YYYY-MM-DD')) >= DATEADD('MONTH', 1, SIGNUP)
AND ((parsed_data."TIMESTAMP"::timestamp_ntz ) < CURRENT_DATE())
)
GROUP BY 1,2
)