Prime Real Estate

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

Asked in

Google

Problem

Each row of devices is one user's device and the browser it runs, and a device type earns a dedicated Chrome layout only once at least six of those users are on Chrome. List the device types that qualify, with their Chrome user counts.

Not every screen earns the build. Find the ones that do.

Tables

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 is really about

This is a row filter versus group filter problem wearing a device-prioritization costume. Anyone can write the COUNT(*) and the GROUP BY. What separates candidates is putting each condition where its column exists. Chrome is a fact about each row, so it belongs in WHERE, before anything is grouped. The six-user minimum is a fact about a bucket, so it belongs in HAVING, after the groups exist. If you count across every browser and test the threshold there, every device type clears six with 40 devices apiece, wearable included. The true Chrome counts are six for four types and five for wearable.

Working through it

Step 1: Filter to Chrome first

browser is a property of each record, so it filters rows before any grouping. WHERE browser = 'Chrome' keeps 29 of the 200 rows. The 28 rows with a NULL browser fail the equality, because a comparison with NULL is never true, so they drop out with no extra handling.

Step 2: Bucket by device_type and count

GROUP BY device_type collapses the surviving rows into one bucket per device type, and COUNT(*) tallies each bucket. The filter has already run, so this is a clean Chrome-only count: four device types at six and 'wearable' at five.

Step 3: Threshold the groups

The 'at least six' rule is about the bucket, not any single row, so it lives in HAVING COUNT(*) >= 6 and is evaluated after grouping. On the seed it drops 'wearable' and keeps the other four.

The solution

SELECT device_type, COUNT(*) AS chrome_users
FROM devices
WHERE browser = 'Chrome'
GROUP BY device_type
HAVING COUNT(*) >= 6

Threshold over every browser

Drop the WHERE and keep HAVING COUNT(*) >= 6: every device type has 40 devices, so all five pass and each reports 40. The count measures the wrong population.

Filter rows, then threshold groups

WHERE browser = 'Chrome' first, then HAVING COUNT(*) >= 6: four device types at six, and 'wearable' is dropped at five.

Common follow-up questions

• What if 'Chrome' shows up with different casing across rows? (Tests normalizing with `LOWER(browser) = 'chrome'` when the source data is dirty.)
• How would you count distinct people instead of devices? (Needs a `user_id` column and `COUNT(DISTINCT user_id)`; probes awareness of devices versus people.)
• How would you surface device types with no Chrome devices at all? (Tests the outer-join instinct: a filtered count never emits a zero row for a type that has no Chrome devices.)