SQL query for user reactivations

Question

I am trying to build a query to count user reactivations per month, where "reactivation" is defined as (for e.g. March 2021):

Sent activity during, or before, January 2021
Did not send activity during February 2021
Sent activity during March 2021

(so 1 or more full calendar months of no activity as the threshold for inactive).

The source table F_ACTIVITY is a per-user per-day time series with columns:

dt (date), user_id, is_active (boolean).

The desired outcome is a table showing:

month, reactivations_this_month

The closest I can get is a count of reactivations in the current month, or something relative to the current date with more case statements (e.g. repeating for current month -2):

  SELECT
COUNT(*) AS reactivations_this_month
FROM

(SELECT
* FROM

(SELECT
user_id,
SUM(current_month_active) AS cma,
SUM(last_month_active) AS lma,
SUM(historical_active) AS h_a
FROM

(SELECT
user_id,
dt,

CASE WHEN DATE_TRUNC(MONTH, DT) = ADD_MONTHS(DATE_TRUNC(MONTH, CURRENT_TIMESTAMP), 0) THEN 1 ELSE 0 END AS current_month_active,
CASE WHEN DATE_TRUNC(MONTH, DT) = ADD_MONTHS(DATE_TRUNC(MONTH, CURRENT_TIMESTAMP), -1) THEN 1 ELSE 0 END AS last_month_active,
CASE WHEN DATE_TRUNC(MONTH, DT) < ADD_MONTHS(DATE_TRUNC(MONTH, CURRENT_TIMESTAMP), -1) THEN 1 ELSE 0 END AS historical_active

FROM F_ACTIVITY
WHERE is_active = 1
) AS x

GROUP BY user_id) AS y

WHERE cma > 0
AND lma = 0
AND h_a > 0) AS z

Any help transforming this into a rolling monthly query greatly appreciated - thanks all!

Final note: I'm trying this in Snowflake, so the dialect is SnowSQL

user_1144035 · Answer

First summarize by month and user, then use lag():

SELECT yyyymm,
       SUM(CASE WHEN prev_yyyymm < yyyymm - INTERVAL '1 month' THEN 1 ELSE 0 END) as num_reactivations
FROM (SELECT user_id, DATE_TRUNC(MONTH, DT) as yyyymm,
             LAG(DATE_TRUNC(MONTH, DT)) OVER (PARTITION BY user_id ORDER BY DATE_TRUNC(MONTH, DT)) as prev_yyyymm
      FROM F_ACTIVITY
      WHERE is_active = 1
      GROUP BY user_id, DATE_TRUNC(MONTH, DT)
     ) um
GROUP BY yyyymm;

SQL query for user reactivations

1 Answers