Count active accounts per month over a year in Snowflake

Question

I am using Snowflake to try to figure out a way to count the number of active clients per month over a set period i.e. last 12 months.

The code I have only counts the total number of active clients over the whole year, what i need to see is if client A is active in Jan and Feb but not in Mar but active again in Apr then my current code would show that this client is active, ignoring the fact that they werent active in Mar.

I want this broken down by month and year and grouped by month and a count of active clients per month.

My current code looks like this:

SELECT
    A.account_billing_country,
    Count (distinct a.acc_id) as active_clients
FROM
    Payments p
INNER JOIN accounts a
    ON a.acc_id = p.acc_id
WHERE date(transfered_at) >= dateadd(year,-1, current date)
GROUP BY 1

Any help appreciated

Answer 1

This query looks at active Stack Overflow users asking questions for each tag, looking at the whole year for previous activity to count the # of actives reported for each month:

select tag, report_date, count(distinct q_owner) one_year_actives
from so_qa_tag
join (select distinct date_trunc(month, q_date) report_date from so_qa_tag)
where q_date between dateadd(year, -1, report_date) and report_date
and report_date > '2012-01-01'
and tag in('java', 'javascript', 'python', 'c#', 'sql')
group by tag, report_date
order by tag, report_date

The secret is to do a join with a list of months (report_date), and look at all activity in the table within a year before that date (q_date between dateadd(year, -1, report_date) and report_date).