# Device Mix

> The device breakdown before the redesign.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The product team is deciding whether to invest in a mobile redesign and needs to understand the current device landscape. For each combination of device type and operating system, show how many devices are registered. Only include combinations that have at least three registrations, ordered from most common to least.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests multi-column GROUP BY with HAVING and ORDER BY. Grouping by two dimensions and filtering on count probes basic aggregation skills.

---

### Break down the requirements

#### Step 1: Group by device type and OS

`GROUP BY device_type, os_name` with `COUNT(*)`.

#### Step 2: Filter and sort

`HAVING COUNT(*) >= 3` removes rare combinations. `ORDER BY COUNT(*) DESC` ranks most common first.

---

### The solution

**Two-dimensional group with HAVING**

```sql
SELECT device_type, os_name, COUNT(*) AS device_count
FROM devices
GROUP BY device_type, os_name
HAVING COUNT(*) >= 3
ORDER BY device_count DESC
```

> **Cost Analysis**
>
> Scan of 2M rows. The GROUP BY produces (device types x OS names) rows, typically under 100. Trivially fast.

> **Interviewers Watch For**
>
> Whether the candidate groups by both columns correctly. Grouping by only one dimension would collapse the other.

> **Common Pitfall**
>
> Forgetting HAVING and adding WHERE COUNT(*) >= 3 is a syntax error. Aggregate filters must use HAVING.

---

## Common follow-up questions

- How would you also show the OS version breakdown? _(Add os_version to the GROUP BY for more granularity.)_
- How would you show only the most common OS per device type? _(Tests first-per-group pattern with RANK.)_
- What if you needed a pivot table with device types as columns? _(Tests CASE WHEN pivot or CROSSTAB.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/device_mix)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.