AMeta’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.
((current_month_dau - previous_month_dau) / previous_month_dau) * 100NULL or 0.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 |
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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity row identifier |
| activity_date | DATE | Date of Instagram Reels activity |
| user_id | INT | Meta user identifier |
| country | VARCHAR(50) | Country associated with the user activity |
| event_name | VARCHAR(50) | Instagram Reels event type such as reel_view or ig_save |
{"reels_daily_activity":[["1","2024-02-10","201","BR","reel_view"],["2","2024-01-05","101","US","reel_view"],["3","2024-03-15","401","DE","reel_view"],["4","2024-01-12","103","IN","reel_view"],["5","2024-02-03","101","US","reel_view"],["6","2024-03-02","301","JP","reel_view"],["7","2024-02-03","104","US","reel_share"],["8","2024-01-05","102","US","ig_save"],["9","2024-03-02","302","JP","ig_save"],["10","2024-02-10","202","BR","reel_view"],["11","2024-01-05","101","US","reel_view"],["12","2024-01Output[["2024-02-01","BR","2.00","1.00","100.00"],["2024-03-01","DE","3.00","1.50","100.00"],["2024-03-01","JP","3.00","2.00","50.00"],["2024-02-01","IN","2.00","1.50","33.33"],["2024-02-01","DE","1.50","1.50","0.00"]]