Task
Meta’s Instagram Reels team wants to flag users whose weekly engagement fell sharply, which can be useful when monitoring AARRR engagement trends or diagnosing a novelty effect after a product change. Write a SQL query to find users whose total engagement dropped by more than 50% week over week.
Requirements
- Treat
engagement_count as the weekly engagement signal for each user.
- Aggregate engagement by user and calendar week using
DATE_TRUNC('week', event_date).
- Compare each user’s weekly total to their immediately previous active week.
- Return only users where the current week is less than 50% of the previous week.
- Exclude rows where the previous week’s engagement is
NULL or 0.
- Return:
user_id, week_start, current_week_engagement, previous_week_engagement, and drop_pct.
- Order results by
drop_pct descending, then user_id, then week_start.
Table Definitions
reels_engagement_events
| Column | Type | Description |
|---|
| event_id | INT | Primary key for the event row |
| user_id | INT | Instagram user identifier |
| event_date | DATE | Date of the engagement event |
| surface | VARCHAR(50) | Meta surface where the event happened |
| engagement_type | VARCHAR(50) | Type of engagement action |
| engagement_count | INT | Count contributed by the row |
Sample Data
reels_engagement_events
| event_id | user_id | event_date | surface | engagement_type | engagement_count |
|---|
| 7 | 103 | 2024-05-14 | ig_reels | like | 10 |
| 1 | 101 | 2024-05-07 | ig_reels | like | 40 |
| 10 | 104 | 2024-05-06 | ig_reels | like | 0 |
| 3 | 101 | 2024-05-14 | ig_reels | like | 15 |
| 12 | 105 | 2024-05-13 | fb_groups | comment | 40 |
| 5 | 102 | 2024-05-07 | ig_reels | ig_save | 20 |
| 9 | 103 | 2024-05-21 | ig_reels | comment | 5 |
| 2 | 101 | 2024-05-08 | ig_reels | ig_save | 20 |
| 11 | 104 | 2024-05-13 | ig_reels | like | 10 |
| 4 | 101 | 2024-05-15 | ig_reels | comment | 10 |
| 8 | 103 | 2024-05-20 | ig_reels | like | 5 |
| 6 | 102 | 2024-05-14 | ig_reels | ig_save | 8 |
Expected Output
| user_id | week_start | current_week_engagement | previous_week_engagement | drop_pct |
|---|
| 102 | 2024-05-13 | 8 | 20 | 60.00 |
| 101 | 2024-05-13 | 25 | 60 | 58.33 |
