Ad-Free Sessions

The mechanism

An ad belongs to a session when it shares the customer and its timestamp falls inside the session's inclusive [start_time, end_time] window; so you join Ads to Playback on customer_id with the timestamp between the bounds, collect every session_id that interval-overlap produces, and return the Playback rows whose id is not in that set. The whole query is an anti-join: keep the rows on the left that have no match on the right.

The problem

Table Playback — one row per viewing session (session_id is the primary key, NOT NULL); a session runs over the inclusive interval start_time .. end_time, and two sessions for the same customer never overlap. Table Ads records each ad shown to a customer at a single timestamp. Report every session that had no ad shown during it.

Worked example

Sample data:

Now test each ad against the sessions for its own customer:

The subquery's distinct set of sessions-with-ads is {1, 4}. The outer query keeps every Playback.session_id not in that set: {2, 3, 5}. Note session 5's window [2, 8] contains no ad timestamp for customer 2 (their ads are at 17 and 20), so it survives.

The solution

Build the set of sessions that did get an ad, then subtract it:

SELECT session_id
FROM   Playback
WHERE  session_id NOT IN (
         SELECT DISTINCT P.session_id
         FROM   Playback P
         JOIN   Ads A ON A.customer_id = P.customer_id
         WHERE  A.timestamp BETWEEN P.start_time AND P.end_time
       );

BETWEEN is inclusive on both ends, which exactly matches "inclusive interval" in the spec — an ad fired at the same instant a session starts or ends still counts. The DISTINCT only tidies duplicates (session 4 matched two ads); it is not required for correctness because NOT IN tests membership, not multiplicity.

Pitfalls

The NOT IN + NULL footgun

This query is safe only because the subquery selects P.session_id, a primary key that is NOT NULL. The moment a subquery can emit a single NULL, NOT IN collapses to zero rows. The reason is three-valued logic: x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL, and x <> NULL is UNKNOWN, never TRUE — so the whole AND can never be TRUE, only FALSE or UNKNOWN, and WHERE keeps neither. If someone later rewrites the subquery to select a nullable column (say A.customer_id after a LEFT JOIN), the page silently returns an empty result with no error.

Safer alternatives that ignore NULLs

NOT EXISTS is correlated and NULL-immune — it asks "does any matching ad row exist?" and a NULL row simply fails to match:

SELECT session_id
FROM   Playback P
WHERE  NOT EXISTS (
         SELECT 1 FROM Ads A
         WHERE  A.customer_id = P.customer_id
           AND  A.timestamp BETWEEN P.start_time AND P.end_time
       );

Or the anti-join via LEFT JOIN ... IS NULL, which keeps left rows whose join found nothing:

SELECT P.session_id
FROM   Playback P
LEFT JOIN Ads A
       ON A.customer_id = P.customer_id
      AND A.timestamp BETWEEN P.start_time AND P.end_time
WHERE  A.ad_id IS NULL;

Test the IS NULL on a column that is NOT NULL in matched rows (the right table's PK, A.ad_id) — otherwise a genuinely-null matched column would be mistaken for "no match."

Inclusive vs half-open intervals

The spec says inclusive, so BETWEEN is right. If you reflexively write A.timestamp > P.start_time AND A.timestamp < P.end_time (strict), you would wrongly call session 1 ad-free — its ad fired at timestamp = 5 = end_time, exactly the boundary. Always confirm whether the interval is inclusive [a,b] or half-open [a,b) before choosing your operators.

Why the naive version goes wrong

A tempting first cut joins on customer only and forgets the timestamp window:

-- WRONG: flags every session of any customer who ever saw an ad
SELECT session_id FROM Playback
WHERE customer_id NOT IN (SELECT customer_id FROM Ads);

Customer 2 saw ads at 17 and 20, so this excludes all of customer 2's sessions — including sessions 3 and 5, which contain no ad. "Ad-free" is a property of the session interval, not the customer; the predicate must pin each ad to a specific window with BETWEEN start_time AND end_time.

Takeaways

• This is an interval-overlap anti-join: a point (the ad timestamp) falls inside a session's inclusive window, and you return the sessions with no such point.
• NOT IN (subquery) is correct here purely by accident of the schema — the projected column is a NOT NULL primary key. Reach for NOT EXISTS or LEFT JOIN ... IS NULL as the default; they are immune to the NULL collapse and usually plan as efficiently.
• Match your range operators to the interval semantics: inclusive bounds → BETWEEN / ≤; boundary timestamps are real edge cases that strict < silently drops.
• Anchor an exclusion to the right grain — the session window, not the customer — or you over-exclude.

Based on LeetCode 1731 “Ad-Free Sessions” (problem statement and sample data). Three-valued-logic and NOT IN/NOT EXISTS behavior per the SQL standard and the PostgreSQL documentation on subquery expressions and NULL comparison. Re-authored and deepened for this guide: added the interval-overlap mechanism, a step-by-step ad-vs-window trace, a timeline diagram, the NOT IN + NULL footgun with NOT EXISTS / LEFT JOIN IS NULL alternatives, and the inclusive-interval edge case.