A Meta Product Growth Analyst is reviewing Instagram Reels engagement as part of the AARRR funnel. Some time_spent_seconds values are NULL because the client failed to log watch time for a subset of sessions.
Write a SQL query to calculate the average time spent on Reels by surface, while handling NULL values correctly.
surface and the average of time_spent_seconds as avg_time_spent_seconds.NULL watch-time values from the average calculation.event_date is '2024-06-01'.avg_time_spent_seconds in descending order.reels_watch_events
| column | type | description |
|---|---|---|
| event_id | INT | Unique event identifier |
| user_id | INT | Meta user identifier |
| surface | VARCHAR(50) | Reels entry surface in Instagram |
| time_spent_seconds | INT | Time spent in seconds; may be NULL if logging failed |
| event_date | DATE | Event date |
| event_id | user_id | surface | time_spent_seconds | event_date |
|---|---|---|---|---|
| 1 | 101 | Home Feed | 45 | 2024-06-01 |
| 2 | 102 | Explore | NULL | 2024-06-01 |
| 3 | 103 | Home Feed | 30 | 2024-06-01 |
| 4 | 104 | Reels Tab | 90 | 2024-06-01 |
| 5 | 105 | Explore | 60 | 2024-06-01 |
| 6 | 106 | Home Feed | NULL | 2024-06-01 |
| surface | avg_time_spent_seconds |
|---|---|
| Reels Tab | 75.00 |
| Explore | 60.00 |
| Home Feed | 37.50 |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | Meta user identifier |
| surface | VARCHAR(50) | Instagram Reels entry surface |
| time_spent_seconds | INT | Time spent watching Reels in seconds; NULL indicates missing logging |
| event_date | DATE | Date of the watch event |
| event_id | user_id | surface | time_spent_seconds | event_date |
|---|---|---|---|---|
| 1 | 101 | Home Feed | 45 | 2024-06-01 |
| 2 | 102 | Explore | 2024-06-01 | |
| 3 | 103 | Home Feed | 30 | 2024-06-01 |
| 4 | 104 | Reels Tab | 90 | 2024-06-01 |
| 5 | 105 | Explore | 60 | 2024-06-01 |
| 6 | 106 | Home Feed | 2024-06-01 | |
| 7 | 107 | Reels Tab | 60 | 2024-06-01 |
| 8 | 108 | Profile | 0 | 2024-06-01 |
| 9 | 109 | Explore | 120 | 2024-05-31 |
| 10 | 110 | Profile | 2024-06-01 | |
| 11 | 107 | Marketplace | 78 | 2024-06-04 |
| 12 | 106 | Ads Placement | 99 | 2024-06-03 |
| 13 | 108 | FB Groups Crosspost | null | 2024-06-03 |
| 14 | 105 | Explore | -21 | 2024-05-30 |
| 15 | 106 | Search | 76 | 2024-05-31 |
| 16 | 104 | Marketplace | 5 | 2024-06-02 |
| 17 | 104 | Reels Tab | 135 | 2024-05-29 |
| 18 | 105 | Watch Tab | 54 | 2024-06-04 |
| 19 | 109 | Ads Placement | null | 2024-05-29 |
| 20 | 100 | Events | 96 | 2024-06-01 |
| 21 | 105 | Stories Tray | null | 2024-05-28 |
| 22 | 105 | Explore | 110 | 2024-06-04 |
| 23 | 111 | DM Share | 117 | 2024-05-29 |
| 24 | 107 | Hashtag Page | 50 | 2024-05-30 |
| 25 | 103 | Following Feed | 89 | 2024-06-03 |
| surface | avg_time_spent_seconds |
|---|---|
| Events | 96.00 |
| Reels Tab | 75.00 |
| Explore | 60.00 |
| Home Feed | 37.50 |
| Profile | 0.00 |