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 |