Number of Accounts That Did Not Stream - Problem
Given two tables Subscriptions and Streams, write an SQL query to find the number of accounts that bought a subscription in 2021 but did not have any stream session.
Table: Subscriptions
account_id(int): Primary key representing unique account identifierstart_date(date): Start date of subscriptionend_date(date): End date of subscription
Table: Streams
session_id(int): Primary key for stream sessionaccount_id(int): Foreign key referencing Subscriptions tablestream_date(date): Date of streaming session
Return the count of accounts that subscribed in 2021 but never streamed.
Table Schema
Subscriptions
| Column Name | Type | Description |
|---|---|---|
account_id
PK
|
int | Primary key - unique account identifier |
start_date
|
date | Start date of subscription |
end_date
|
date | End date of subscription |
Primary Key: account_id
Streams
| Column Name | Type | Description |
|---|---|---|
session_id
PK
|
int | Primary key - unique stream session identifier |
account_id
|
int | Foreign key referencing Subscriptions.account_id |
stream_date
|
date | Date when streaming session occurred |
Primary Key: session_id
Input & Output
Example 1 — Mixed Streaming Activity
Input Tables:
Subscriptions
| account_id | start_date | end_date |
|---|---|---|
| 1 | 2021-02-20 | 2021-10-20 |
| 13 | 2021-05-20 | 2021-09-20 |
| 15 | 2021-03-20 | 2021-08-20 |
Streams
| session_id | account_id | stream_date |
|---|---|---|
| 8 | 1 | 2021-06-21 |
Output:
| accounts_count |
|---|
| 2 |
💡 Note:
Among the three accounts that subscribed in 2021, only account 1 has streaming activity. Accounts 13 and 15 subscribed in 2021 but never streamed, so the count is 2.
Example 2 — All Accounts Stream
Input Tables:
Subscriptions
| account_id | start_date | end_date |
|---|---|---|
| 1 | 2021-02-20 | 2021-10-20 |
| 2 | 2021-05-20 | 2021-09-20 |
Streams
| session_id | account_id | stream_date |
|---|---|---|
| 8 | 1 | 2021-06-21 |
| 9 | 2 | 2021-07-21 |
Output:
| accounts_count |
|---|
| 0 |
💡 Note:
Both accounts that subscribed in 2021 have streaming activity, so no accounts qualify. The result is 0.
Example 3 — No 2021 Subscriptions
Input Tables:
Subscriptions
| account_id | start_date | end_date |
|---|---|---|
| 1 | 2020-02-20 | 2020-10-20 |
| 2 | 2022-05-20 | 2022-09-20 |
Streams
| session_id | account_id | stream_date |
|---|
Output:
| accounts_count |
|---|
| 0 |
💡 Note:
No accounts subscribed in 2021, so the count is 0 regardless of streaming activity.
Constraints
-
1 ≤ account_id ≤ 10^6 -
start_date < end_date -
Dates are in YYYY-MM-DD format -
account_id in Streams references valid account_id in Subscriptions
Visualization
Tap to expand
Understanding the Visualization
1
Input Tables
Subscriptions and Streams data
2
LEFT JOIN
Match subscribers with streams
3
Count Result
Count accounts without streams
Key Takeaway
🎯 Key Insight: Use LEFT JOIN to preserve all subscribers and identify those without matching stream records
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code