# Prime Real Estate

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

Canonical URL: <https://datadriven.io/problems/prime_real_estate>

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.

## Tables

### devices

- device_id INTEGER
- device_type TEXT
- os_name TEXT
- os_version TEXT
- browser TEXT

Sample rows (10 of 200):

| device_id | device_type | os_name | os_version | browser |
| --- | --- | --- | --- | --- |
| 201 | desktop | Android | 15.1 | Chrome |
| 230 | tablet | Windows | 16.2 | Firefox |
| 259 | smart_tv | macOS | 17.0 | Edge |
| 288 | wearable | Linux | 11 | Opera |
| 317 | mobile | tvOS | 12 | Samsung Internet |
| 346 | desktop | watchOS | 13 | NULL |
| 375 | tablet | iOS | 10.0.19041 | Safari |
| 404 | smart_tv | Android | 14.1 | Chrome |
| 433 | wearable | Windows | NULL | Firefox |
| 462 | mobile | macOS | 14.0 | Edge |

## Expected output

First 4 of 4 rows:

| device_type | chrome_users |
| --- | --- |
| desktop | 6 |
| mobile | 6 |
| smart_tv | 6 |
| tablet | 6 |

## 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

**Filter in `WHERE`, threshold in `HAVING`**

```sql
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.

> **Exactly six is the whole answer**
>
> Every qualifying device type sits at exactly six Chrome devices. Write `HAVING COUNT(*) > 6` and the result is empty. 'At least six' is inclusive, so the comparison is `>=`.

> **Where each condition lands is the tell**
>
> Put `COUNT(*) >= 6` in `WHERE` and the engine rejects it, because no aggregate exists before grouping. A candidate who puts the browser test in `WHERE` and the count test in `HAVING` without hesitating knows the order the engine runs things in.

> **One scan, five tiny groups**
>
> At the production table's 3M rows this is one sequential scan filtered to Chrome, then a hash aggregate over five device types. There is no join, no subquery and nothing to index around.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/prime_real_estate)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.