Checking user retention and churn in SQL
Question
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.