Device Mix

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

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

The product team is scoping a redesign of the web experience, so it only cares about devices that actually run a browser. For each pairing of device type and operating system, count how many such devices are registered, keeping only the pairings with at least three.

The device breakdown before the redesign.

Tables

devices

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

Expected output

Worked solution and explanation

Two filters, two different places

This looks like a plain count per group, but it is really a two-stage filter in a device-inventory costume. One filter is row-level: keep only devices that recorded a browser. The other is group-level: keep only pairings with COUNT(*) >= 3. The whole problem turns on knowing that those two live in different clauses. Put the count test in WHERE and the query errors out. Put the browser test after the counts are formed and every watchOS and iOS pairing carries devices you were supposed to throw away first.

Why WHERE and HAVING are not interchangeable

WHERE runs per row, before any grouping happens, so it is the right home for browser IS NOT NULL: that is a fact about a single device. HAVING runs per group, after aggregation, so it is the only place a COUNT(*) test can live: the count does not exist until the rows are grouped. Swap them and neither works. WHERE COUNT(*) >= 3 has no group to count, and a browser check after grouping fires too late, after browserless rows have already inflated the totals.

Step 1: Filter the rows first

Drop the browserless devices with WHERE browser IS NOT NULL. This is a per-row decision, so it belongs in WHERE, and it runs before the counts are formed. On this data all 28 browserless devices sit on 'watchOS' or 'iOS', so skipping it inflates exactly those pairings.

Step 2: Group by the composite key

GROUP BY device_type, os_name builds one group per pairing, and COUNT(*) counts the surviving rows in each. Grouping by both columns together is what keeps 'desktop'/'Android' separate from 'tablet'/'Android'; grouping by one column would fuse them and inflate the count.

Step 3: Filter the groups

HAVING COUNT(*) >= 3 drops the sparse pairings the product team called noise. On this data it removes 'desktop'/'watchOS' and 'mobile'/'iOS', the two pairings with only two browser-recording devices, leaving 33 of 35.

The solution

SELECT device_type, os_name, COUNT(*) AS device_count
FROM devices
WHERE browser IS NOT NULL
GROUP BY device_type, os_name
HAVING COUNT(*) >= 3

Common follow-up questions

• How would you break this down further by `os_version` as well? (Pushes them to add a third grouping column and think about how nulls in `os_version` interact with the count.)
• How would you return only the single most common operating system per device type? (Tests the first-per-group pattern with a window function over the grouped counts.)
• What if the product team wanted device types as columns instead of rows? (Tests conditional aggregation to pivot device types into columns.)