Subscribed Accounts Without 2021 Streaming Activity

Medium

A streaming platform operations team wants to measure passive subscribers who stayed eligible during 2021 but never played content in that year. Table: Subscriptions account_id: unique account identifier start_date: first active subscription day end_date: last active subscription day each account appears exactly once start_date is strictly earlier than end_date Table: Streams session_id: unique stream session identifier account_id: account that generated the stream stream_date: calendar day when the stream occurred each stream belongs to an account from Subscriptions Definitions: A subscription is active during 2021 if [start_date, end_date] overlaps the inclusive calendar interval [2021-01-01, 2021-12-31]. An account streamed in 2021 if it has at least one stream with stream_date in [2021-01-01, 2021-12-31]. Task: Count how many accounts were active during 2021 but did not stream in 2021. Output requirements: Return exactly one row. Return exactly one column named accounts_count. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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  |

Output

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

Example 2

Input

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  |

Output

[
  {"accounts_count":2}
]

Explanation

Boundary days are inclusive. Account 201 is disqualified by a 2021 stream, while accounts 202 and 203 remain counted.

Example 3

Input

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  |

Output

[
  {"accounts_count":0}
]

Explanation

Neither subscription overlaps 2021, so the count is zero even though streams exist in 2021.

Constraints

0 <= number of rows in Subscriptions <= 250000
0 <= number of rows in Streams <= 500000
1 <= account_id <= 1000000000
1 <= session_id <= 1000000000
Subscriptions.account_id is unique
Streams.session_id is unique
Each Streams.account_id appears in Subscriptions.account_id
All date fields are valid date strings (YYYY-MM-DD)
Subscriptions.start_date < Subscriptions.end_date
A subscription overlaps 2021 iff start_date <= 2021-12-31 and end_date >= 2021-01-01
Return exactly one column: accounts_count
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with one column accounts_count

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 |

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 |

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 |