Skip to Content
Learn
Common Metrics
1 Day Retention 3

Now that we have our gameplays table joined to itself, we can start to calculate retention.

1 Day Retention is defined as the number of players who returned the next day divided by the number of original players, per day. Suppose 10 players played Mineblocks on Dec 10th. If 4 of them play on Dec 11th, the 1 day retention for Dec 10th is 40%.

Instructions

1.

The previous query joined all rows in gameplays against all other rows for each user, making a massive result set that we don’t need.

We’ll need to modify this query.

select date(g1.created_at) as dt, g1.user_id, g2.user_id from gameplays as g1 join gameplays as g2 on g1.user_id = g2.user_id and /**/ order by 1 limit 100;

Complete the query above such that the join clause includes a date join:

date(g1.created_at) = date(datetime(g2.created_at, '-1 day'))

This means “only join rows where the date in g1 is one less than the date in g2“, which makes it possible to see if users have returned!

2.

The query above won’t return meaningful results because we’re using an inner join. This type of join requires that the condition be met for all rows, effectively limiting our selection to only the users that have returned.

Instead, we want to use a left join, this way all rows in g1 are preserved, leaving nulls in the rows from g2 where users did not return to play the next day.

Change the join clause to use left join and count the distinct number of users from g1 and g2 per date.

select date(g1.created_at) as dt, count(distinct g1.user_id) as total_users, count(distinct g2.user_id) as retained_users from gameplays as g1 /**/ gameplays as g2 on g1.user_id = g2.user_id and date(g1.created_at) = date(datetime(g2.created_at, '-1 day')) group by 1 order by 1 limit 100;
3.

Now that we have retained users as count(distinct g2.user_id) and total users as count(distinct g1.user_id), divide retained users by total users to calculate 1 day retention!

select date(g1.created_at) as dt, round(100 * count(/**/) / count(/**/)) as retention from gameplays as g1 left join gameplays as g2 on g1.user_id = g2.user_id and date(g1.created_at) = date(datetime(g2.created_at, '-1 day')) group by 1 order by 1 limit 100;
Folder Icon

Sign up to start coding

Already have an account?