MySQL Forums
Forum List  »  Newbie

Rolling retention
Posted by: Kristie Focht
Date: November 23, 2018 09:39AM

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

Options: ReplyQuote


Subject
Written By
Posted
Rolling retention
November 23, 2018 09:39AM
November 23, 2018 11:23AM
November 23, 2018 12:31PM
November 23, 2018 02:56PM
November 23, 2018 03:52PM
November 23, 2018 08:39PM
December 03, 2018 10:13AM
December 03, 2018 12:32PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.