Rolling retention
Hello! I'm trying to calculate rolling retention of users. I find SQL queries, but I don't know how to re-create in MySQL.
I have a table called 'user' that contains userId, and created (the date the account was created). I have a second table called activity_log, which contains userId and created (each date the userId had an activity, or was active).
I'm wanting to know:
How many users were created in June. Of those users, how many were active in Month 1 (July), and Month 2 (August), and Month 3 (September), etc. through current date.
How many users were created in July. Of those users, how many were active in Month 1 (August), and Month 2 (September), and Month 3 (October), etc. through current date.
Repeat above for each month through current date.
The SQL equivalent I found is as follows. Copy/paste with substitutes with my database tables user and activity_log does not work, so I'm assuming there are some things that need to be converted to MySQL. I appreciate any help anyone has!
with cohort_items as (
select
date_trunc('month', U.created_at)::date as cohort_month,
id as user_id
from public.users U
order by 1, 2
),
-- (user_id, month_number): user X has activity in month number X
user_activities as (
select
A.user_id,
MONTH_DIFF(
date_trunc('month', A.created_at)::date,
C.cohort_month
) as month_number
from public.activities A
left join cohort_items C ON A.user_id = C.user_id
group by 1, 2
),
-- (cohort_month, size)
cohort_size as (
select cohort_month, count(1) as num_users
from cohort_items
group by 1
order by 1
),
-- (cohort_month, month_number, cnt)
B as (
select
C.cohort_month,
A.month_number,
count(1) as num_users
from user_activities A
left join cohort_items C ON A.user_id = C.user_id
group by 1, 2
)
-- our final value: (cohort_month, size, month_number, percentage)
select
B.cohort_month,
S.num_users as total_users,
B.month_number,
B.num_users::float * 100 / S.num_users as percentage
from B
left join cohort_size S ON B.cohort_month = S.cohort_month
order by 1, 3
where B.cohort_month IS NOT NULL