Uninterrupted Playback Sessions

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

ads =

[{"ad_id":1,"customer_id":1,"timestamp":5},{"ad_id":2,"customer_id":2,"timestamp":17},{"ad_id":3,"customer_id":2,"timestamp":20}]

playback =

[{"session_id":1,"customer_id":1,"start_time":1,"end_time":5},{"session_id":2,"customer_id":1,"start_time":15,"end_time":23},{"session_id":3,"customer_id":2,"start_time":10,"end_time":12},{"session_id":4,"customer_id":2,"start_time":17,"end_time":28},{"session_id":5,"customer_id":2,"start_time":2,"end_time":8}]

Uninterrupted Playback Sessions

Hints

Uninterrupted Playback Sessions

Hints