Loading problem...
A streaming platform operations team wants to measure passive subscribers who stayed eligible during 2021 but never played content in that year.
Table: Subscriptions
Table: Streams
Definitions:
Task: Count how many accounts were active during 2021 but did not stream in 2021.
Output requirements:
Supported submission environments:
Subscriptions:
| account_id | start_date | end_date |
|------------|------------|------------|
| 101 | 2020-03-10 | 2021-09-30 |
| 102 | 2021-01-15 | 2021-12-15 |
| 103 | 2022-02-01 | 2022-12-01 |
| 104 | 2020-11-01 | 2021-01-20 |
| 105 | 2019-04-01 | 2020-06-30 |
Streams:
| session_id | account_id | stream_date |
|------------|------------|-------------|
| 9001 | 101 | 2020-11-12 |
| 9002 | 102 | 2021-05-10 |
| 9003 | 104 | 2020-12-31 |
| 9004 | 104 | 2022-01-01 |[
{"accounts_count":2}
]Explanation
Accounts 101 and 104 overlap 2021 and never stream in 2021. Account 102 streams in 2021, and accounts 103 and 105 do not overlap 2021.
Subscriptions:
| account_id | start_date | end_date |
|------------|------------|------------|
| 201 | 2021-01-01 | 2021-12-31 |
| 202 | 2020-06-01 | 2021-01-01 |
| 203 | 2021-12-31 | 2022-03-01 |
Streams:
| session_id | account_id | stream_date |
|------------|------------|-------------|
| 1 | 201 | 2021-12-31 |
| 2 | 202 | 2020-12-31 |
| 3 | 203 | 2022-01-01 |[
{"accounts_count":2}
]Explanation
Boundary days are inclusive. Account 201 is disqualified by a 2021 stream, while accounts 202 and 203 remain counted.
Subscriptions:
| account_id | start_date | end_date |
|------------|------------|------------|
| 301 | 2019-01-01 | 2019-12-31 |
| 302 | 2022-01-01 | 2023-01-01 |
Streams:
| session_id | account_id | stream_date |
|------------|------------|-------------|
| 77 | 301 | 2021-03-05 |
| 78 | 302 | 2021-06-06 |[
{"accounts_count":0}
]Explanation
Neither subscription overlaps 2021, so the count is zero even though streams exist in 2021.
Constraints