D1/D7 Retention
ASKED IN INTERVIEWMeta
Problem Statement
Analyze user stickiness for the January 2024 cohort. Calculate the percentage of users who were active on Day 1 and Day 7 post-signup.
Rules:
- Output columns: day_1_retention, day_7_retention.
- Formula: (Users back on Day X / Total Users in Cohort) * 100.
- Cohort: Users with signup_date in January 2024.
- Round to 2 decimal places.
Tests your understanding of
Self Join, Retention and Date Arithmetic
Input Tables
users
| user_id(INTEGER) | signup_date(DATE) |
|---|---|
| 1 | 2024-01-01 |
| 2 | 2024-01-01 |
| 3 | 2024-02-01 |
user_activity
| user_id(INTEGER) | activity_date(DATE) |
|---|---|
| 1 | 2024-01-02 |
| 1 | 2024-01-08 |
| 2 | 2024-01-02 |
device_metadata
| user_id(INTEGER) | device_type(VARCHAR) |
|---|---|
| 1 | iOS |
| 2 | Android |
Expected Output
| day_1_retention(DECIMAL) | day_7_retention(DECIMAL) |
|---|---|
| 100 | 50 |
Tags
HardASKED IN INTERVIEWSelf JoinRetentionDate Arithmetic
35-45 min
34%