The Faithful Few

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

Quora

Problem

Each user_sessions row is one session on one device, and devices holds each device's type, so a user's history can span several types. Count, per device type, the users who have never been seen on any other type, most loyal type first.

Loyal to one platform only.

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

What this problem really is

Strip the costume and this is a per-user set-cardinality test wearing a product-analytics disguise. A user is loyal to a platform only when the set of device_type values across all of their sessions has exactly one member. Anyone can filter rows to device_type = 'mobile'. What separates candidates is seeing that a row filter answers 'who ever used mobile', never 'who used mobile and nothing else'. Get that wrong and every multi-device user is counted once for each type they touched, inflating every platform.

Break down the requirements

Step 1: Collapse each user's sessions to their set of types

Inner-join user_sessions to devices on device_id so every session carries its type, then group by user_id and keep only the users whose COUNT(DISTINCT device_type) is 1, in a HAVING. Sessions with a NULL device_id find no device and drop out of the join, so they neither add a type nor disqualify anyone.

Step 2: Re-count the survivors by their one type

Each surviving user has exactly one type, so MIN(device_type) reads it back unambiguously: the minimum of a one-value set is that value. That leaves one row per loyal user in exclusive_users, and a second GROUP BY device_type with COUNT(*) gives each platform's loyal base. The order of the two aggregations is the whole problem: filter per user first, count per type second.

Row filter

Join, then GROUP BY device_type with COUNT(DISTINCT user_id), counts everyone who ever opened a session on that type. A user with tablet and smart TV sessions lands in both totals.

Per-user constraint

GROUP BY user_id with HAVING COUNT(DISTINCT device_type) = 1 first, so that same user is dropped entirely, and only single-type users reach the per-type count.

The solution

WITH exclusive_users AS (
    SELECT s.user_id, MIN(d.device_type) AS device_type
    FROM user_sessions s
    JOIN devices d ON s.device_id = d.device_id
    GROUP BY s.user_id
    HAVING COUNT(DISTINCT d.device_type) = 1
)
SELECT device_type, COUNT(*) AS exclusive_user_count
FROM exclusive_users
GROUP BY device_type
ORDER BY exclusive_user_count DESC

Common follow-up questions

• How would you find users on exactly two device types? (Changes the `HAVING` threshold, and `MIN` with `MAX` then reads both types.)
• What if `device_type` itself could be `NULL`? (Whether a missing type should break exclusivity, given that `COUNT(DISTINCT ...)` skips it.)
• How would you list each multi-device user's device types? (Collecting a set per user with `STRING_AGG` or `ARRAY_AGG`.)