Uninterrupted Playback Sessions

Easy

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 session_id: unique identifier of a viewing session customer_id: viewer identifier start_time: session start timestamp (integer) end_time: session end timestamp (integer) Table: ads ad_id: unique identifier of an ad impression event customer_id: viewer who saw the ad timestamp: ad impression timestamp (integer) Data guarantees: start_time <= end_time for every playback row. Sessions for the same customer do not overlap. session_id is unique. ad_id is unique. Session time windows are inclusive: [start_time, end_time]. 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: Return exactly one column: session_id Sort by session_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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        |

Output

[
  {"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.

Example 2

Input

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       |

Output

[
  {"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.

Example 3

Input

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) |

Output

[
  {"session_id":20},
  {"session_id":21}
]

Explanation

With no ad events, every playback session is uninterrupted.

Constraints

0 <= number of rows in playback <= 300000
0 <= number of rows in ads <= 300000
1 <= session_id <= 1000000000
1 <= ad_id <= 1000000000
1 <= customer_id <= 1000000000
0 <= start_time <= end_time <= 1000000000
0 <= timestamp <= 1000000000
session_id is unique
ad_id is unique
For each customer_id, playback intervals do not overlap
Return exactly one column: session_id
Sort output by session_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with column: session_id

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 |

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 |

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) |