Task
StreamWave's analytics team noticed attribution events are down by roughly 15% in the last 24 hours. Write a SQL query to identify the likely root cause by comparing the most recent 24-hour window to the prior 24-hour window.
Your query should surface the dimensions with the largest absolute drop in successful attribution events so an analyst can quickly see whether the issue is concentrated in a specific source, platform, or pipeline status.
Requirements
- Compare successful attribution events in the last 24 hours against the previous 24 hours.
- Break results out by
source, platform, and status.
- Return only rows where the last 24 hours had fewer successful events than the prior 24 hours.
- Include the absolute drop and percentage drop, ordered by the largest absolute drop first.
Table Definitions
attribution_events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| user_id | INT | User tied to the attribution event |
| campaign_id | INT | Campaign tied to the event |
| event_time | TIMESTAMP | Time the attribution event was recorded |
| source | VARCHAR(50) | Attribution source such as SDK or API |
| platform | VARCHAR(20) | Client platform |
| status | VARCHAR(20) | Processing status |
| | |
campaigns
| column | type | description |
|---|
| campaign_id | INT | Campaign identifier |
| campaign_name | VARCHAR(100) | Campaign name |
| channel | VARCHAR(50) | Marketing channel |
| is_active | BOOLEAN | Whether the campaign is active |
| | |
users
| column | type | description |
|---|
| user_id | INT | User identifier |
| country | VARCHAR(50) | User country |
| signup_platform | VARCHAR(20) | User signup platform |
| is_test_user | BOOLEAN | Whether the user is a test account |
Sample Data
attribution_events
| event_id | user_id | campaign_id | event_time | source | platform | status |
|---|
| 1001 | 1 | 101 | 2024-06-10 10:00:00 | sdk | ios | attributed |
| 1002 | 2 | 101 | 2024-06-10 11:00:00 | sdk | ios | attributed |
| 1003 | 3 | 102 | 2024-06-10 12:00:00 | api | android | attributed |
| 1004 | 4 | 103 | 2024-06-10 13:00:00 | sdk | web | failed |
| 1005 | 5 | 104 | 2024-06-11 02:00:00 | sdk | ios | attributed |
| 1006 | 6 | 102 | 2024-06-11 03:00:00 | api | android | failed |
Expected Output
| source | platform | status | previous_24h_events | last_24h_events | absolute_drop | pct_drop |
|---|
| sdk | ios | attributed | 4 | 1 | 3 | 75.00 |
| api | android | attributed | 2 | 1 | 1 | 50.00 |
