Loading problem...
A streaming telemetry system stores watch sessions and ad impression events in separate tables. Product analysts need to identify sessions in which viewers watched content continuously without any ad exposure.
Table: playback
Table: ads
Data guarantees:
Task: Return every session_id from playback for which no ad from the same customer happened during that session's inclusive time window.
Output requirements:
Supported submission environments:
playback:
| session_id | customer_id | start_time | end_time |
|------------|-------------|------------|----------|
| 1 | 1 | 1 | 5 |
| 2 | 1 | 15 | 23 |
| 3 | 2 | 10 | 12 |
| 4 | 2 | 17 | 28 |
| 5 | 2 | 2 | 8 |
ads:
| ad_id | customer_id | timestamp |
|------|-------------|-----------|
| 1 | 1 | 5 |
| 2 | 2 | 17 |
| 3 | 2 | 20 |[
{"session_id":2},
{"session_id":3},
{"session_id":5}
]Explanation
Session 1 gets an ad at timestamp 5, and session 4 gets ads at 17 and 20. Sessions 2, 3, and 5 have no in-window ads for their customers.
playback:
| session_id | customer_id | start_time | end_time |
|------------|-------------|------------|----------|
| 10 | 7 | 100 | 120 |
| 11 | 7 | 130 | 140 |
ads:
| ad_id | customer_id | timestamp |
|------|-------------|-----------|
| 1 | 7 | 120 |
| 2 | 7 | 141 |[
{"session_id":11}
]Explanation
The interval is inclusive, so timestamp 120 counts as an ad inside session 10. Timestamp 141 is outside session 11 and does not disqualify it.
playback:
| session_id | customer_id | start_time | end_time |
|------------|-------------|------------|----------|
| 20 | 9 | 5 | 9 |
| 21 | 9 | 15 | 20 |
ads:
| ad_id | customer_id | timestamp |
|------|-------------|-----------|
| (no rows) |[
{"session_id":20},
{"session_id":21}
]Explanation
With no ad events, every playback session is uninterrupted.
Constraints