Ad-Free Sessions - Practice Coding Problems

Ad-Free Sessions - Problem

Database Easy

You're working for a video streaming platform and need to identify which viewing sessions were completely ad-free.

Given two tables:

Playback - Contains customer viewing sessions with start and end times
Ads - Contains ad impressions with timestamps for each customer

Your task is to find all sessions where no ads were shown during the viewing period. An ad is considered shown during a session if its timestamp falls within the inclusive time interval [start_time, end_time].

Goal: Return all session_ids that had zero ads displayed during their runtime.

Input & Output

example_1.sql — Basic Case

$ Input: Playback: | session_id | customer_id | start_time | end_time | |------------|-------------|------------|---------| | 1 | 101 | 10 | 50 | | 2 | 102 | 20 | 60 | | 3 | 101 | 70 | 90 | Ads: | ad_id | customer_id | timestamp | |-------|-------------|-----------| | 1 | 101 | 25 | | 2 | 102 | 15 |

› Output: | session_id | |------------| | 2 | | 3 |

💡 Note: Session 1 has ad 1 at timestamp 25 (within [10,50]). Session 2 has ad 2 at timestamp 15 (outside [20,60]). Session 3 has no ads during [70,90]. So sessions 2 and 3 are ad-free.

example_2.sql — All Sessions Have Ads

$ Input: Playbook: | session_id | customer_id | start_time | end_time | |------------|-------------|------------|---------| | 1 | 101 | 0 | 30 | | 2 | 102 | 10 | 40 | Ads: | ad_id | customer_id | timestamp | |-------|-------------|-----------| | 1 | 101 | 15 | | 2 | 102 | 25 |

› Output: | session_id | |------------|

💡 Note: Session 1 has ad 1 at timestamp 15 (within [0,30]). Session 2 has ad 2 at timestamp 25 (within [10,40]). No sessions are ad-free.

example_3.sql — No Ads Table

$ Input: Playback: | session_id | customer_id | start_time | end_time | |------------|-------------|------------|---------| | 1 | 101 | 10 | 50 | | 2 | 102 | 20 | 60 | Ads: | ad_id | customer_id | timestamp | |-------|-------------|-----------|

› Output: | session_id | |------------| | 1 | | 2 |

💡 Note: No ads exist in the database, so all sessions are ad-free by default.

Visualization

Tap to expand

Understanding the Visualization

Collect Sessions

Gather all customer viewing sessions with their time intervals

Gather Ad Data

Collect all ad impressions with timestamps and customer IDs

Match Overlaps

For each session, find ads that played during the viewing window

Filter Ad-Free

Return only sessions that had zero matching ad impressions

Key Takeaway

🎯 Key Insight: Use LEFT JOIN to preserve all sessions, then filter WHERE ad_id IS NULL to find sessions with no overlapping ads - this gives us the ad-free viewing experiences!

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Single pass through both tables with efficient JOIN operation

✓ Linear Growth

Space Complexity

O(1)

No additional space beyond result set, database handles JOIN internally

✓ Linear Space

Constraints

1 ≤ session_id, ad_id ≤ 10⁵
1 ≤ customer_id ≤ 10⁴
0 ≤ start_time ≤ end_time ≤ 10⁶
0 ≤ timestamp ≤ 10⁶
Two sessions for the same customer do not intersect
All session_ids and ad_ids are unique

Asked in

N Netflix 45 a Amazon 38 H Hulu 28 ▷ YouTube 22

The optimal solution uses a LEFT JOIN between Playback and Ads tables to match sessions with overlapping ads, then filters for sessions where ad_id IS NULL to find ad-free sessions. Time complexity is O(n + m) where n is sessions and m is ads.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Subquery for Each Session)	O(n*m)	O(1)	Check each session individually against all ads using correlated subqueries
LEFT JOIN with NULL Filter (Optimal)	O(n + m)	O(1)	Use LEFT JOIN to match sessions with ads, then filter for sessions with no matches

Brute Force (Subquery for Each Session) — Algorithm Steps

Select all sessions from Playback table
For each session, count ads where customer matches AND timestamp is between start_time and end_time
Filter sessions where the ad count equals zero
Return the session_ids

Visualization

Tap to expand

Step-by-Step Walkthrough

Select Session

Pick a session from Playback table

Scan All Ads

Check every ad to see if it overlaps with this session

Count Matches

Count ads that fall within session time interval

Filter Result

Keep session if count equals zero

Code -

solution.c — C

// SQL Solution as string
const char* sqlQuery = 
"SELECT session_id "
"FROM Playback p "
"WHERE ( "
"    SELECT COUNT(*) "
"    FROM Ads a "
"    WHERE a.customer_id = p.customer_id "
"    AND a.timestamp >= p.start_time "
"    AND a.timestamp <= p.end_time "
") = 0 "
"ORDER BY session_id;";

// C implementation
#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int session_id, customer_id, start_time, end_time;
} Session;

typedef struct {
    int ad_id, customer_id, timestamp;
} Ad;

int compare(const void* a, const void* b) {
    return (*(int*)a - *(int*)b);
}

int* findAdFreeSessions(Session* sessions, int sessionCount, 
                       Ad* ads, int adCount, int* resultSize) {
    int* result = malloc(sessionCount * sizeof(int));
    *resultSize = 0;
    
    for (int i = 0; i < sessionCount; i++) {
        int adMatchCount = 0;
        for (int j = 0; j < adCount; j++) {
            if (ads[j].customer_id == sessions[i].customer_id &&
                ads[j].timestamp >= sessions[i].start_time &&
                ads[j].timestamp <= sessions[i].end_time) {
                adMatchCount++;
            }
        }
        
        if (adMatchCount == 0) {
            result[(*resultSize)++] = sessions[i].session_id;
        }
    }
    
    qsort(result, *resultSize, sizeof(int), compare);
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n*m)

For each of n sessions, we scan through m ads to count matches

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond the result set

✓ Linear Space

Constraints

1 ≤ session_id, ad_id ≤ 10⁵
1 ≤ customer_id ≤ 10⁴
0 ≤ start_time ≤ end_time ≤ 10⁶
0 ≤ timestamp ≤ 10⁶
Two sessions for the same customer do not intersect
All session_ids and ad_ids are unique

23.4K Views

Medium Frequency

~15 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Subquery for Each Session) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler