Task
Meta’s Instagram Reels analytics team wants to identify which countries are growing fastest in monthly engagement. Using daily activity logs, write a SQL query to find the top 5 countries by month-over-month growth in DAU.
Assume DAU means the number of distinct active users per country per day, and monthly DAU for this question means the average daily DAU within each month.
Requirements
- Compute daily DAU from the activity table using distinct users.
- Aggregate daily DAU into monthly average DAU for each country.
- Calculate month-over-month growth rate as:
((current_month_dau - previous_month_dau) / previous_month_dau) * 100
- Exclude country-months where the previous month’s DAU is
NULL or 0.
- Return the top 5 country-month rows with the highest growth rate.
- Include: month, country, current month average DAU, previous month average DAU, and growth rate.
Table Definitions
reels_daily_activity
| column_name | type | description |
|---|
| activity_id | INT | Unique row identifier |
| activity_date | DATE | Date of user activity on Instagram Reels |
| user_id | INT | Meta user identifier |
| country | VARCHAR(50) | User country |
| event_name | VARCHAR(50) | Activity type |
Sample Data
reels_daily_activity
| activity_id | activity_date | user_id | country | event_name |
|---|
| 1 | 2024-01-05 | 101 | US | reel_view |
| 2 | 2024-01-05 | 102 | US | ig_save |
| 3 | 2024-01-05 | 101 | US | reel_view |
| 4 | 2024-01-12 | 103 | IN | reel_view |
| 5 | 2024-02-03 | 101 | US | reel_view |
| 6 | 2024-02-03 | 104 | US | reel_share |
| 7 | 2024-02-10 | 201 | BR | reel_view |
| 8 | 2024-03-02 | 301 | JP | reel_view |
| 9 | 2024-03-02 | 302 | JP | ig_save |
| 10 | 2024-03-15 | 401 | DE | reel_view |
Expected Output
| month_start | country | current_month_avg_dau | previous_month_avg_dau | mom_growth_pct |
|---|
| 2024-02-01 | BR | 2.00 | 1.00 | 100.00 |
| 2024-03-01 | JP | 3.00 | 2.00 | 50.00 |
| 2024-02-01 | US | 2.00 | 1.50 | 33.33 |
| 2024-03-01 | IN | 2.00 | 1.50 | 33.33 |
| 2024-03-01 | DE | 1.50 | 1.33 | 12.50 |
