Active Users: MoM
Linked In
Problem Statement
LinkedIn wants to measure user retention. A user is considered retained in a given month if they had at least one activity in that month AND at least one activity in the immediately preceding month.
Write an SQL query to find the number of retained users for each month.
Rules:
- The output should show the month (as an integer or date) and the count of retained users.
- Retained users = Users active in Month X who were also active in Month X-1.
- Output columns: month, active_users_count.
- Results must be sorted by month in ascending order.
Tests your understanding of
Self-Join, Date Arithmetic and Aggregation
Input Tables
User_Logins
| user_id(INTEGER) | event_date(TIMESTAMP) |
|---|---|
| 1 | 2026-01-15 10:00:00 |
| 1 | 2026-02-01 12:00:00 |
| 2 | 2026-02-10 14:00:00 |
| 3 | 2026-01-20 09:00:00 |
| 3 | 2026-02-05 11:00:00 |
User_Profiles
| user_id(INTEGER) | signup_date(DATE) |
|---|---|
| 1 | 2025-12-01 |
Login_Devices
| user_id(INTEGER) | device_type(VARCHAR) |
|---|---|
| 1 | Mobile |
Expected Output
| month(INTEGER) | active_users_count(INTEGER) |
|---|---|
| 2 | 2 |
Tags
MediumSelf-JoinDate ArithmeticAggregation
25-30 min
42%