Number of Accounts That Did Not Stream - Solution & Explanation

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.