Netflix SQL Interview Question: Detect Multi-Device Login Overlaps (FAANG-Level)

One of the most common — and tricky — SQL interview patterns at FAANG companies involves time-based overlaps.

In this blog, we’ll solve a real Netflix-style SQL interview problem step by step, using both self-joins and window functions, and clearly explain the core overlap logic that many candidates get wrong.

---

Problem Statement

Netflix tracks user login sessions across devices.
Each session has:

• user_id
• device_type
• login_time
• logout_time

👉 Goal:
Identify users who were logged in on multiple devices at the same time.

This logic is used to detect:

• Concurrent streaming
• Account sharing
• Suspicious usage patterns

---

📊 Sample Dataset (SQL Server)

CREATE TABLE netflix_sessions (
    session_id INT PRIMARY KEY,
    user_id INT,
    device_type VARCHAR(20),
    login_time DATETIME,
    logout_time DATETIME
);

INSERT INTO netflix_sessions VALUES
(1, 101, 'Mobile',  '2025-01-01 10:00', '2025-01-01 11:00'),
(2, 101, 'TV',      '2025-01-01 10:30', '2025-01-01 12:00'),
(3, 101, 'Laptop', '2025-01-01 13:00', '2025-01-01 14:00'),
(4, 102, 'Mobile', '2025-01-02 09:00', '2025-01-02 10:00'),
(5, 102, 'TV',     '2025-01-02 10:00', '2025-01-02 11:00'),
(6, 103, 'Tablet', '2025-01-03 08:00', '2025-01-03 09:30'),
(7, 103, 'Mobile', '2025-01-03 09:00', '2025-01-03 10:00');

---

🚨 The Core SQL Trap: Time Overlaps

Many candidates try to use BETWEEN — and fail.

❌ Incorrect approach

s1.login_time BETWEEN s2.login_time AND s2.logout_time

This misses partial overlaps.

---

✅ Correct Time Overlap Logic (Must-Know)

Two time windows overlap if and only if:

s1.login_time < s2.logout_time
AND s2.login_time < s1.logout_time

Why this works:

• Captures partial overlaps
• Captures fully nested sessions
• Avoids false positives when sessions just touch

📌 Interview rule to remember:

Two sessions overlap if each one starts before the other ends.

---

🧩 Solution 1: Self-Join Approach (Most Explicit)

This approach compares every session with every other session for the same user.

SQL Server Query

SELECT DISTINCT
    s1.user_id,
    s1.device_type AS device_1,
    s2.device_type AS device_2,
    s1.login_time,
    s1.logout_time,
    s2.login_time,
    s2.logout_time
FROM netflix_sessions s1
JOIN netflix_sessions s2
    ON s1.user_id = s2.user_id
   AND s1.device_type <> s2.device_type
   AND s1.login_time < s2.logout_time
   AND s2.login_time < s1.logout_time
   AND s1.session_id < s2.session_id;

Key Points

• device_type <> ensures multi-device usage
• Time conditions detect overlaps
• session_id < session_id removes mirrored duplicates

✔ Best when you need exact overlapping device pairs

---

⚡ Solution 2: Window Function (No Self-Join)

This approach is more efficient and interview-impressive.

Idea

1. Sort sessions by login time per user
2. Compare each session with the previous session
3. Detect overlaps using LAG()

SQL Server Query

WITH ordered_sessions AS (
    SELECT
        session_id,
        user_id,
        device_type,
        login_time,
        logout_time,
        LAG(device_type) OVER (
            PARTITION BY user_id
            ORDER BY login_time
        ) AS prev_device,
        LAG(logout_time) OVER (
            PARTITION BY user_id
            ORDER BY login_time
        ) AS prev_logout
    FROM netflix_sessions
)
SELECT DISTINCT user_id
FROM ordered_sessions
WHERE
    prev_device IS NOT NULL
    AND device_type <> prev_device
    AND login_time < prev_logout;

Why this works

• Overlapping sessions must appear adjacent when sorted
• Runs in O(n) instead of O(n²)
• No self-join required

✔ Best when performance matters

---

Self-Join vs Window Function

Approach	Lists Device Pairs	Performance	Interview Use
Self-Join	✅ Yes	Slower	Safe baseline
Window Function	❌ No	Faster	Advanced edge

---

🎯 FAANG Interview Tip

If you understand time overlaps, you unlock:

• Session analysis
• Booking conflicts
• Concurrent usage
• Fraud detection

This pattern appears in Netflix, Uber, Meta, Stripe, and Amazon interviews.

---

📘 Want More FAANG SQL Practice?

If you want to practice real interview-level SQL problems like this one, check out the FAANG SQL Practice Workbook — 50+ problems with step-by-step solutions covering:

• Time overlaps
• Window functions
• Joins & subqueries
• Real-world datasets

👉 https://knowstar.gumroad.com/l/gyaous

---

🔔 Final Thoughts

Mastering overlap logic is less about SQL syntax and more about thinking in time ranges.

Once this clicks, advanced SQL interview questions become much easier.