Number of Accounts That Did Not Stream - Solution & Explanation
Problem Statement
Table: Subscriptions
+-------------+------+ | Column Name | Type | +-------------+------+ | account_id | int | | start_date | date | | end_date | date | +-------------+------+ account_id is the primary key column for this table. Each row of this table indicates the start and end dates of an account's subscription. Note that always start_date < end_date.
Table: Streams
+-------------+------+ | Column Name | Type | +-------------+------+ | session_id | int | | account_id | int | | stream_date | date | +-------------+------+ session_id is the primary key column for this table. account_id is a foreign key from the Subscriptions table. Each row of this table contains information about the account and the date associated with a stream session.
Write an SQL query to report the number of accounts that bought a subscription in 2021 but did not have any stream session.
The query result format is in the following example.
Example 1:
Input: Subscriptions table: +------------+------------+------------+ | account_id | start_date | end_date | +------------+------------+------------+ | 9 | 2020-02-18 | 2021-10-30 | | 3 | 2021-09-21 | 2021-11-13 | | 11 | 2020-02-28 | 2020-08-18 | | 13 | 2021-04-20 | 2021-09-22 | | 4 | 2020-10-26 | 2021-05-08 | | 5 | 2020-09-11 | 2021-01-17 | +------------+------------+------------+ Streams table: +------------+------------+-------------+ | session_id | account_id | stream_date | +------------+------------+-------------+ | 14 | 9 | 2020-05-16 | | 16 | 3 | 2021-10-27 | | 18 | 11 | 2020-04-29 | | 17 | 13 | 2021-08-08 | | 19 | 4 | 2020-12-31 | | 13 | 5 | 2021-01-05 | +------------+------------+-------------+ Output: +----------------+ | accounts_count | +----------------+ | 2 | +----------------+ Explanation: Users 4 and 9 did not stream in 2021. User 11 did not subscribe in 2021.
Approach Overview
Problem Overview: Count how many accounts had an active subscription during 2021 but did not stream any content in 2021. The database provides two tables: Subscriptions (subscription period per account) and Streams (dates when an account streamed). The task is essentially filtering accounts with valid subscription overlap in 2021 and excluding those with stream activity in that same year.
Approach 1: Anti-Join with LEFT JOIN (O(S + T) time, O(1) extra space)
This solution models the problem as an anti-join between the Subscriptions and Streams tables. First, filter subscriptions that overlap the 2021 calendar year using date comparisons such as start_date <= '2021-12-31' and end_date >= '2021-01-01'. Then perform a LEFT JOIN to the Streams table restricted to stream events occurring in 2021. If the joined stream record is NULL, that account had no streaming activity during the year. Finally, count those accounts.
The key insight: SQL can efficiently represent “records in table A with no matching record in table B” using a left join followed by a WHERE ... IS NULL filter. Database engines typically optimize this into a hash or nested-loop anti-join. The time complexity is roughly O(S + T), where S is the number of subscription rows and T is the number of stream records scanned for 2021. Space overhead stays O(1) at the query level because the database manages intermediate join structures internally.
Approach 2: NOT EXISTS Subquery (O(S + T) time, O(1) extra space)
Another common pattern is a correlated NOT EXISTS subquery. Iterate through the filtered set of subscriptions active in 2021 and check that no stream record exists for the same account_id within the 2021 date range. The NOT EXISTS clause stops searching as soon as it finds a matching stream, which can be efficient with proper indexing on Streams(account_id, stream_date).
This method expresses the logic more directly: “count subscriptions where no 2021 stream exists.” Query planners often transform this into the same anti-join execution plan used by the LEFT JOIN approach. Complexity remains O(S + T) in practice since both tables are scanned or indexed by account and date.
Both approaches rely on core database querying techniques, particularly filtering by date ranges and eliminating matches using anti-joins. Understanding these patterns is essential for many SQL interview problems involving activity logs, subscriptions, or user engagement data. The anti-join concept also appears frequently in relational joins questions.
Recommended for interviews: Use the LEFT JOIN ... IS NULL anti-join pattern. It clearly shows you understand how to exclude matching rows across tables. Mentioning NOT EXISTS as an equivalent alternative demonstrates deeper SQL knowledge and awareness of query planner optimizations.
Solution
Code
MySQL
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| LEFT JOIN Anti-Join | O(S + T) | O(1) | Best general solution. Clear logic for excluding rows with matching stream records. |
| NOT EXISTS Subquery | O(S + T) | O(1) | Useful when expressing “no matching row exists.” Often optimized to the same execution plan as anti-join. |
Video Solution
Leetcode MEDIUM 2020 - Accounts That Did Not Stream - SQL Explained by Everyday Data Science • Everyday Data Science • 448 views views
Frequently Asked Questions
Is Number of Accounts That Did Not Stream easy or hard?
Number of Accounts That Did Not Stream Python/Java solution
How to solve Number of Accounts That Did Not Stream in O(n)?
What is the best approach for Number of Accounts That Did Not Stream?
Is Number of Accounts That Did Not Stream asked at Google/Amazon/Meta?
What data structure is used in Number of Accounts That Did Not Stream?
What is the time complexity of Number of Accounts That Did Not Stream?
Ready to solve this problem?
Practice Number of Accounts That Did Not Stream with our built-in code editor and test cases.
Practice on FleetCode