Device Type Serving Most Users

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Netflix

Problem

The device team's reach report crowns a single winner, but the sessions in user_sessions show other device types serving nearly as many unique users. Using each session's device type from devices, list every device type that reaches at least 90% of the leader's unique-user count, along with that count.

One device type serves more users than the rest.

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

devices

Columns: device_id INTEGER, device_type TEXT, os_name TEXT, os_version TEXT, browser TEXT

Expected output

Worked solution and explanation

This is a relative-cutoff filter dressed up as a device leaderboard. Two things must be right at once: the reach number counts people, not sessions, and the bar is measured against a leader the query has to find first. Anyone writes the join and the COUNT(DISTINCT us.user_id). What separates candidates is refusing to treat 'the most' as one row. On this data 'smart_tv' and 'tablet' tie at 13 and 'desktop' sits at 12, so a sort-and-take-one answer returns a third of the front-runners, and a bar computed with integer math quietly lets the two 11s in.

Building it

Step 1: Count people per device type

Inner-join user_sessions to devices on device_id and take COUNT(DISTINCT us.user_id) per device_type. Users have several sessions each, and one user ran on 'desktop', 'tablet' and 'smart_tv' in the same morning, so COUNT(*) measures traffic, not reach. Sessions with a NULL device_id match no device and drop out, which is correct: they have no type to credit.

Step 2: Name the per-type counts once

Put the aggregate in a CTE, type_users. The final filter reads it twice, once row by row and once to find the leader, and the CTE makes both reads use the same numbers.

Step 3: Compare against the leader, not a constant

(SELECT MAX(user_count) FROM type_users) is one value, 13 here, and 0.9 * turns it into the bar, 11.7. Every type at or above it survives: the two 13s and the 12. However the top ties, and however many types fall inside the band, they all come back.

WITH type_users AS (
    SELECT
        d.device_type,
        COUNT(DISTINCT us.user_id) AS user_count
    FROM user_sessions us
    JOIN devices d ON us.device_id = d.device_id
    GROUP BY d.device_type
)
SELECT device_type, user_count
FROM type_users
WHERE user_count >= 0.9 * (SELECT MAX(user_count) FROM type_users)

Sort and take one

ORDER BY user_count DESC LIMIT 1 returns whichever 13 the engine emits first and never sees 'desktop'. Raising it to LIMIT 3 happens to match today, but it hardcodes a number of front-runners nobody asked for.

Band around the leader

WHERE user_count >= 0.9 * (SELECT MAX(user_count) FROM type_users) returns every type that clears the bar, however many there are and however the top ties.

Common follow-up questions

• How would you return the top three device types by reach, keeping every type tied at the third spot? (Tests whether the candidate reaches for a tie-aware ordering rather than a bare `LIMIT 3`.)
• The device team now wants the 90% band per operating system. What changes? (The leader stops being one global value and becomes one per `os_name`, so the maximum must be scoped to each group.)
• What if a session should count toward reach only when it lasted at least a minute? (A row filter on `session_duration_sec` before counting, plus a decision about its `NULL` durations.)