Modules
Data Analyst
Data Engineer
Product Data Scientist
AI Engineer
Machine Learning Engineer
Prompt Engineer
You've recently joined TikTok as a Senior Data Analyst, and your team is interested in gaining insights into the monthly viewer retention for the year 2023 using the users and viewer_events tables.
Viewer retention is calculated as the percentage of users who were viewers in the current month (M1) and remained viewers in the subsequent month (M2).
users table:
| Column Name | Description |
|---|---|
| user_id | Unique identifier for each user. |
| username | User's username on TikTok. |
| country | User's country of residence. |
| signup_date | Date when the user signed up on TikTok. |
viewer_events table:
| Column Name | Description |
|---|---|
| user_id | Identifier of the user viewing a TikTok video |
| event_date | Date when the user viewed a Tiktok video |
| month | m1_m2_retention |
|---|---|
| 2023-01-01 00:00:00 | 0.529.. |
| 2023-02-01 00:00:00 | 0.555... |
| 2023-03-01 00:00:00 | 0.6875... |
| ... | ... |
| 2023-12-01 00:00:00 | 0.931... |
You've recently joined TikTok as a Senior Data Analyst, and your team is interested in gaining insights into the monthly viewer retention for the year 2023 using the users and viewer_events tables.
Viewer retention is calculated as the percentage of users who were viewers in the current month (M1) and remained viewers in the subsequent month (M2).
users table:
| Column Name | Description |
|---|---|
| user_id | Unique identifier for each user. |
| username | User's username on TikTok. |
| country | User's country of residence. |
| signup_date | Date when the user signed up on TikTok. |
viewer_events table:
| Column Name | Description |
|---|---|
| user_id | Identifier of the user viewing a TikTok video |
| event_date | Date when the user viewed a Tiktok video |
| month | m1_m2_retention |
|---|---|
| 2023-01-01 00:00:00 | 0.529.. |
| 2023-02-01 00:00:00 | 0.555... |
| 2023-03-01 00:00:00 | 0.6875... |
| ... | ... |
| 2023-12-01 00:00:00 | 0.931... |