Also for churned users it should be count (distinct b.user_id) as a.user_id will be null

Can I ask is this a SQL question? Thanks.

This was a SQL question to query the table with logs

Define monthly active users to be users who are active at least once each month in the log. Since we are dealing with dates, we will convert to time elapsed relative to today, which will be negative for all entries. In pandas, this would be df['time_elapsed'] = df['DateTime'] - datetime.datetime.today() or something similar. To get the number of churned users, we group by 'userID', then sort by time_elapsed, then apply .diff() with a period of 1 to get differences between rows. Finally, we take .max() over this new column to get the maximum time a user hasn't used FB. If that time is greater than a month, the user is churned. We filter over rows with time_elapsed greater than a month and count these users. To get resurrected users, we use our table of churned users and find users who have been active in the last month.

How about take full outer join between current month data and previous month data. when data present in current month then active when data present in previous month but not current month then churned when data not present in previous month but present in current month then resurrected

As Trisha notes, you could use an outer join but that would probably be an expensive operation given that most users have a large number of interactions, but I'm putting that below for reference. Monthly active users: SELECT COUNT(DISTINCT user_id) AS num_active_users FROM user_activity WHERE datetime BETWEEN DATE_ADD(CURRENT_DATE, -30) AND CURRENT_DATE Churned users: SELECT COUNT(DISTINCT a.user_id) as num_churned FROM user_activity a FULL OUTER JOIN user_activity b ON a.user_id = b.user_id WHERE a.datetime BETWEEN DATE_ADD(CURRENT_DATE, -30) AND CURRENT_DATE AND b.datetime BETWEEN DATE_ADD(CURRENT_DATE, -60) AND DATE_ADD(CURRENT_DATE, -31) WHERE a.user_id IS NULL -- user was not active in the last 30 days, but was active in the previous 31-60 days Churned users: SELECT COUNT(DISTINCT a.user_id) as num_resurrected FROM user_activity a FULL OUTER JOIN user_activity b ON a.user_id = b.user_id WHERE a.datetime BETWEEN DATE_ADD(CURRENT_DATE, -30) AND CURRENT_DATE AND b.datetime BETWEEN DATE_ADD(CURRENT_DATE, -60) AND DATE_ADD(CURRENT_DATE, -31) WHERE b.user_id IS NULL -- user was not active in the last 60-31 days but was active in the last 30 days