Number of Accounts That Did Not Stream - Problem
🎬 Streaming Service Analytics
You're working as a data analyst for a popular streaming service! Your company wants to understand subscriber engagement patterns to optimize their business strategy.
You have access to two important datasets:
- Subscriptions Table: Contains account subscription periods with
account_id,start_date, andend_date - Streams Table: Records every streaming session with
session_id,account_id, andstream_date
Your mission: Find how many accounts purchased a subscription in 2021 but never actually used the service (had zero streaming sessions).
This metric is crucial for understanding subscription abandonment - customers who pay but don't engage with the platform. The marketing team needs this data to improve user onboarding and reduce churn!
Return a single number representing the count of inactive subscribers from 2021.
Input & Output
Basic Example
$
Input:
Subscriptions:
+------------+------------+------------+
| account_id | start_date | end_date |
+------------+------------+------------+
| 1 | 2021-01-01 | 2021-12-31 |
| 2 | 2021-03-15 | 2021-06-15 |
| 3 | 2020-12-01 | 2021-02-28 |
+------------+------------+------------+
Streams:
+------------+------------+-------------+
| session_id | account_id | stream_date |
+------------+------------+-------------+
| 101 | 1 | 2021-02-14 |
| 102 | 1 | 2021-03-20 |
+------------+------------+-------------+
›
Output:
1
💡 Note:
Accounts 1 and 2 subscribed in 2021. Account 1 has streaming sessions, but account 2 has no streams. Account 3 started in 2020, so it's not counted. Result: 1 inactive account.
No Inactive Accounts
$
Input:
Subscriptions:
+------------+------------+------------+
| account_id | start_date | end_date |
+------------+------------+------------+
| 1 | 2021-01-01 | 2021-12-31 |
| 2 | 2021-06-01 | 2021-12-31 |
+------------+------------+------------+
Streams:
+------------+------------+-------------+
| session_id | account_id | stream_date |
+------------+------------+-------------+
| 101 | 1 | 2021-02-14 |
| 102 | 2 | 2021-07-01 |
+------------+------------+-------------+
›
Output:
0
💡 Note:
Both accounts that subscribed in 2021 have streaming activity, so no accounts are inactive.
All Accounts Inactive
$
Input:
Subscriptions:
+------------+------------+------------+
| account_id | start_date | end_date |
+------------+------------+------------+
| 1 | 2021-01-01 | 2021-12-31 |
| 2 | 2021-06-01 | 2021-12-31 |
| 3 | 2021-09-01 | 2021-12-31 |
+------------+------------+------------+
Streams:
+------------+------------+-------------+
| session_id | account_id | stream_date |
+------------+------------+-------------+
| 101 | 4 | 2021-02-14 |
+------------+------------+-------------+
›
Output:
3
💡 Note:
All three accounts subscribed in 2021, but none of them have any streaming sessions (account 4 in streams didn't subscribe in 2021). All are inactive.
Visualization
Tap to expand
Understanding the Visualization
1
Identify 2021 Season Ticket Holders
Find all customers who bought season passes in 2021
2
Check Attendance Records
For each ticket holder, see if they appear in any show attendance logs
3
Find No-Shows
Identify ticket holders with zero attendance
4
Count the Result
Return the total number of inactive ticket holders
Key Takeaway
🎯 Key Insight: LEFT JOIN with NULL filtering is the most efficient way to find records that exist in one table but not another - a common pattern in data analysis for identifying inactive users, missing relationships, or gaps in data.
Time & Space Complexity
Time Complexity
O(n + m)
Single pass through subscriptions (n) and streams (m) with hash join
✓ Linear Growth
Space Complexity
O(k)
Space for hash table of smaller relation (k accounts)
✓ Linear Space
Constraints
- 1 ≤ account_id ≤ 106
- start_date < end_date
- Dates are in format YYYY-MM-DD
- session_id is unique in Streams table
- account_id in Streams is a foreign key from Subscriptions
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code