Number of Accounts That Did Not Stream

Number of Accounts That Did Not Stream

What is this problem about?

The Number of Accounts That Did Not Stream SQL problem asks you to find the number of accounts that subscribed in a given year but did not stream any content during that same year. This requires anti-join logic — finding records in one table with no matching records in another.

Why is this asked in interviews?

Warnermedia asks this to test SQL anti-join techniques, a common pattern in subscription analytics: "find customers who signed up but didn't use the product." The database interview pattern requires correctly identifying non-matching rows using LEFT JOIN with NULL check or NOT EXISTS / NOT IN.

Algorithmic pattern used

LEFT JOIN with NULL check or NOT EXISTS.

SELECT COUNT(*) AS accounts_count
FROM Subscriptions s
WHERE s.year = [target_year]
AND s.account_id NOT IN (
    SELECT DISTINCT account_id FROM Streams WHERE year = [target_year]
)

Or equivalently with LEFT JOIN: join Subscriptions to Streams on account_id and year, filter WHERE Streams.account_id IS NULL.

Example explanation

Subscriptions 2021: accounts [1, 2, 3, 4]. Streams 2021: accounts [1, 3]. Accounts that subscribed but didn't stream: [2, 4]. Count = 2.

Common mistakes candidates make

• Using INNER JOIN (returns accounts that DID stream, the opposite of what's needed).
• Not filtering by year in both the subscription and stream conditions.
• Using NOT IN when NULL values exist in the subquery (NOT EXISTS is safer).
• Counting rows instead of distinct account IDs.

Interview preparation tip

Anti-join SQL (finding rows in table A with no match in table B) has three equivalent approaches: LEFT JOIN + IS NULL, NOT EXISTS, and NOT IN. In practice, NOT EXISTS and LEFT JOIN + IS NULL are preferred over NOT IN when NULLs might exist in the subquery (NOT IN returns no rows if any NULL is present). Practice all three forms — interviewers may ask you to rewrite one approach as another. This "unmatched rows" pattern is fundamental in churn analysis, inactive user reporting, and subscription analytics.