SQL Logic for calculating the unique members count for rolling 3 months by each month

0
votes

I am working on a request to count the number of unique registered members in a table for the below date buckets (based on registration_date_utc - timestamp),

Jan 1-Mar 31: Count of Unique registered members

Feb 1-Apr 30: Count of Unique registered members

Mar 1-May 31: Count of Unique registered members

Apr 1-Jun 30: Count of Unique registered members

May 1-Jul 31: Count of Unique registered members

Jun 1-Aug 31: Count of Unique registered members

Jul 1-Sep 30: Count of Unique registered members

Aug 1-Oct 31: Count of Unique registered members

Sep 1-Nov 30: Count of Unique registered members

Oct 1-Dec 31: Count of Unique registered members

Below is my Snowflake query which works but it doesn't return any result,

select
DATE_TRUNC('month',t.registration_date_utc) as "MONTH_START",
count(distinct t.MEMBER_ID)
FROM
(
select
member_id,
registration_date_utc
from  table a
) as t
WHERE TO_DATE(t.registration_date_utc) BETWEEN DATEADD(month,'3',t.registration_date_utc) AND t.registration_date_utc
group by 1
order by 1;
2
sqldatetimestampsnowflake-cloud-data-platform

2 Answers

1
votes

BETWEEN START_DATE and END_DATE You condition for date will always be false

select
DATE_TRUNC('month',t.registration_date_utc) as "MONTH_START",
count(distinct t.MEMBER_ID)
FROM
(
select
member_id,
registration_date_utc
from  table a
) as t
--WHERE TO_DATE(t.registration_date_utc) BETWEEN DATEADD(month,'3',t.registration_date_utc) AND t.registration_date_utc
WHERE TO_DATE(t.registration_date_utc) BETWEEN t.registration_date_utc AND DATEADD(month,'3',t.registration_date_utc) 

group by 1
order by 1;
0
votes

You want to count unique visitors for three months. One method is to expand the table for the count:

SELECT DATE_TRUNC('month', t.registration_date_utc) + v.offset * INTERVAL '1 month') as MONTH_START,
       COUNT(DISTINCT t.MEMBER_ID)
FROM t CROSS JOIN
     (VALUES (0), (1), (2)) v(offset)
GROUP BY 1
ORDER BY 1;