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

The frontend team is choosing which device types deserve a dedicated Chrome layout, and it only commits to a device type once it has at least six Chrome users. Return those device types 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 WHERE-versus-HAVING problem wearing a device-prioritization costume. The real question: do you know that the browser filter is a row-level condition that belongs in WHERE, before anything is grouped, while the six-user minimum is a condition on the aggregate that only HAVING can express? Anyone can write the COUNT and the GROUP BY. The candidates who stumble put COUNT(*) >= 6 in WHERE, where the engine rejects it outright, or push browser = 'Chrome' into HAVING, where it filters nothing sensibly. Either way the answer is wrong before you even look at the counts.

Working through it

Step 1: Filter to Chrome first

Chrome is a property of each individual record, so it filters rows before any grouping happens. That is exactly what WHERE is for. WHERE browser = 'Chrome' throws away every non-Chrome device up front, so everything downstream only ever sees Chrome records.

Step 2: Bucket by device type and count

GROUP BY device_type collapses the surviving rows into one bucket per device type, and COUNT(*) measures how many Chrome devices landed in each bucket. Because the Chrome filter already ran, this count is a clean Chrome-only tally.

Step 3: Threshold the groups, then sort

The 'at least six' rule is a condition on the bucket, not on any single row, so it can only live in HAVING COUNT(*) >= 6 after the groups exist. On the seed this drops wearable, the one device type with only five Chrome users. ORDER BY chrome_users DESC, device_type ASC puts the biggest footholds on top, and because the survivors tie at six the alphabetical tiebreaker gives the list a stable order.

The solution

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

Common follow-up questions

• What if 'Chrome' shows up with different casing across rows? (Tests LOWER(browser) = 'chrome' for case-insensitive matching 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`); surfaces devices-vs-people awareness.)
• How would you surface device types with no Chrome devices at all? (Tests the anti-join instinct: types with zero Chrome devices never appear in a filtered count.)