Daily Retention Summary
0:00:00
Lime’s consumer app team wants to measure the number of users who return to the app after their initial login. Given a user_logins table that records each user’s login by date, write a query to produce a daily summary with retention and engagement metrics.
For each date that appears in the user_logins table, calculate daily retention metrics including cohort size, 7-day retention, retention rate, and rolling 7-day active users. (A cohort for a given date consists of all users who logged in on that specific date (regardless of whether it was their first login or a subsequent login).
Notes:
- A user is considered retained if they log in again within the next 7 days after their cohort date (days 2-8), excluding the cohort day itself.
- The rolling 7-day users includes all distinct users who logged in during the 7-day window ending on the current date (from login_date - 6 days to login_date, inclusive).
- Each (user_id, login_date) pair is unique - users can only log in once per day. The output should be ordered by login_date ascending.
- Retention rate should be calculated as: (retained_within_7d / cohort_users) * 100, rounded to 1 decimal place.
Schema:
Input:
user_logins table
| Column | Type |
|---|---|
user_id |
INTEGER |
login_date |
DATE |
Output:
| Column | Type |
|---|---|
login_date |
DATE |
cohort_users |
INTEGER |
retained_within_7d |
INTEGER |
retention_rate |
DECIMAL(3, 2) |
rolling_7d_users |
INTEGER |
Example:
Input:
| user_id | login_date |
|---|---|
| 1 | 2024-06-01 |
| 2 | 2024-06-01 |
| 1 | 2024-06-02 |
| 2 | 2024-06-08 |
| 3 | 2024-06-08 |
| 1 | 2024-06-09 |
| 3 | 2024-06-10 |
Output:
| login_date | cohort_users | retained_within_7d | retention_rate | rolling_7d_users |
|---|---|---|---|---|
| 2024-06-01 | 2 | 1 | 50.0 | 2 |
| 2024-06-02 | 1 | 1 | 100.0 | 2 |
| 2024-06-08 | 2 | 1 | 50.0 | 2 |
| 2024-06-09 | 1 | 0 | 0.0 | 2 |
| 2024-06-10 | 1 | 0 | 0.0 | 2 |
Explanation:
Retention is calculated for users who return within 7 days after their first login on a given day. Rolling 7-day user counts unique users over the current and previous 6 days.*
Suppose the current login_date is October 10, 2027.
- The cohort for this date includes all users who logged in on 2027-10-10.
- A user is considered retained if they log in again between 2027-10-11 and 2027-10-17 (inclusive).
- The cohort day itself (2027-10-10) is excluded from this retention window.
.
.
.
.
Comments