I have two data sets and want to find the cohort monthly retention rate.

So the total users acquired (cohort size ) is the total number users who have the event account activated.

We need to calculate how many users are still using the app (making deposit or withdrwal) after getting acquired (account activated)

I need to find something like below. Expected Output

User Table :-

• UserID
• Event (Account registered, account verified, account activated)
• Timestamp

Transaction Table :-

• UserID
• Timestamp
• transaction type (deposit, withdrawal)

Expected Result below

| First Month| Cohort Size |M0 | M1 | M2..............
| --------   | --------    |.  |    |
| 1          | Cell 2      |.  |.   |
| 2          | Cell 4      |.  |.   |
| 3.         |             |.  |.   |

Result of the below query

| First Month| M0 | M1 | M2..............
| --------   | --------|
| 1          |    |.   |
| 2          |    |.   |
| 3.         |    |.   |

WITH first_month_transaction AS ( SELECT userid, MIN(EXTRACT(MONTH
FROM timestamp)) AS first_month FROM user where event_category
=’account activated’ GROUP BY userid ), transaction_month AS( SELECT DISTINCT userid, EXTRACT(MONTH FROM timestamp) AS month FROM
transaction where transaction_type in (‘Deposit’, ‘Withdrawal’) ),
join_table as (SELECT tm.userid, tm.month, fmt.userid,
fmt.first_month, tm.month – fmt.first_month AS month_diff FROM
first_month_transaction AS fmt LEFT JOIN transaction_month AS tm ON
tm.userid = fmt.userid)

SELECT first_month, SUM(CASE WHEN month_diff = 0 THEN 1 ELSE 0 END) AS
m0, SUM(CASE WHEN month_diff = 1 THEN 1 ELSE 0 END) AS m1, SUM(CASE
WHEN month_diff = 2 THEN 1 ELSE 0 END) AS m2, SUM(CASE WHEN month_diff
= 3 THEN 1 ELSE 0 END) AS m3, SUM(CASE WHEN month_diff = 4 THEN 1 ELSE 0 END) AS m4, SUM(CASE WHEN month_diff = 5 THEN 1 ELSE 0 END) AS m5,
SUM(CASE WHEN month_diff = 6 THEN 1 ELSE 0 END) AS m6, SUM(CASE WHEN
month_diff = 7 THEN 1 ELSE 0 END) AS m7, SUM(CASE WHEN month_diff = 8
THEN 1 ELSE 0 END) AS m8, SUM(CASE WHEN month_diff = 9 THEN 1 ELSE 0
END) AS m9, SUM(CASE WHEN month_diff = 10 THEN 1 ELSE 0 END) AS m10,
SUM(CASE WHEN month_diff = 11 THEN 1 ELSE 0 END) AS m11 FROM
join_table GROUP BY first_month ORDER BY first_month