Checking user retention and churn in SQL


You're given the following table showing user logins to your system:

Table: login_info

user_id month year
1 1 2021
2 1 2021
3 1 2021
4 1 2021
1 2 2021
2 3 2021
3 2 2021

Using the table above:

  • Write a SQL query that returns the number of retained users per month in 2021. In this case, we'll define retention for each given month as the number of users who logged in that month and also logged in the prior month.
  • Once you've written that query, write one additional query to return the users who did not come back in a given month (e.g. they logged in the month prior, but not the next consecutive month) -- we'll call these our churned users.

You can view/query the data in an interactive SQL fiddle here.


Access restricted

Subscribe to premium account to see the solution.

Get premium now