Hello - I'm trying to determine rolling user retention.
I have a table called 'user' that contains the userId, and the date they created their account.
I have a second table, activity_log, that also has the userId, and the dates they had activity.
I'm looking for a count of userId's by month/year using the date their account was created. And then how many of those users were active a month from that date, then two months, then 3 months, etc.
query 1 would look like:
select count DATE_FORMAT(created, '%m %Y') AS DateCreated, userId
from user
where DATE(created) >= "2015-06-01"
group by 1;
Query 2 would then count how many of those users, by month and year, subsequently had a date in the activity_log from the user.created date +1, +2, +3, +4, etc.
I've seen examples of this for SQL, by week, but I can't get it working in MySQL by month:
WITH by_week
AS (SELECT
user_id,
TD_DATE_TRUNC('week', login_time) AS login_week
FROM logins
GROUP BY 1, 2),
with_first_week
AS (SELECT
user_id,
login_week,
FIRST_VALUE(login_week) OVER (PARTITION BY user_id ORDER BY login_week) AS first_week
FROM by_week),
with_week_number
AS (SELECT
user_id,
login_week,
first_week,
(login_week - first_week) / (24 * 60 * 60 * 7) AS week_number
FROM with_first_week)
SELECT
TD_TIME_FORMAT(first_week, 'yyyy-MM-dd') AS first_week,
SUM(CASE WHEN week_number = 0 THEN 1 ELSE 0 END) AS week_0,
SUM(CASE WHEN week_number = 1 THEN 1 ELSE 0 END) AS week_1,
SUM(CASE WHEN week_number = 2 THEN 1 ELSE 0 END) AS week_2,
SUM(CASE WHEN week_number = 3 THEN 1 ELSE 0 END) AS week_3,
SUM(CASE WHEN week_number = 4 THEN 1 ELSE 0 END) AS week_4,
SUM(CASE WHEN week_number = 5 THEN 1 ELSE 0 END) AS week_5,
SUM(CASE WHEN week_number = 6 THEN 1 ELSE 0 END) AS week_6,
SUM(CASE WHEN week_number = 7 THEN 1 ELSE 0 END) AS week_7,
SUM(CASE WHEN week_number = 8 THEN 1 ELSE 0 END) AS week_8,
SUM(CASE WHEN week_number = 9 THEN 1 ELSE 0 END) AS week_9
FROM with_week_number
GROUP BY 1
ORDER BY 1
