# Everyone Counts

> Every account lands in a segment, even the ones with nothing on file.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The growth team is profiling the whole user base by age, and plenty of accounts still have no age on file. Show each age bucket that holds more than one user along with its user count, most populated first, and count the accounts with no bucket recorded as a segment of their own.

## Worked solution and explanation

### Why this problem exists in real interviews

On the surface this is the friendliest question on the board: group, count, keep the buckets with more than one user, sort. Everyone writes it in thirty seconds. The separator is a single reflex. The moment you see a nullable dimension, do you reach for `WHERE age_bucket IS NOT NULL`? Here that reflex is wrong. The accounts with no age recorded are a bucket too, and dropping them quietly deletes an entire segment from a demographics profile. The interviewer is watching whether you exclude nulls on autopilot or because the business actually asked you to.

---

### Break down the requirements

#### Step 1: Group by age bucket

`GROUP BY age_bucket` produces one row per demographic segment, and the rows with a null age_bucket collapse into a single group of their own. That group is exactly the missing-age segment the growth team asked you to keep, so there is no WHERE filter on age_bucket.

#### Step 2: Filter and sort

`HAVING COUNT(*) > 1` keeps buckets with multiple users and gates the grouped result, not the base rows. `ORDER BY user_count DESC` puts the most populated demographics first.

---

### The solution

**Group, filter, and sort by count**

```sql
SELECT age_bucket, COUNT(*) AS user_count
FROM users
GROUP BY age_bucket
HAVING COUNT(*) > 1
ORDER BY user_count DESC
```

> **Cost Analysis**
>
> Single scan of 10M rows. Age buckets are low cardinality (under 20 groups, including the null segment), so the aggregation is trivially cheap.

> **Common Pitfall**
>
> The instinct is to slap on `WHERE age_bucket IS NOT NULL` and move on. Here that is the wrong call: the accounts with no age on file are a genuine segment, and a null forms its own group that clears the same `HAVING COUNT(*) > 1` threshold as any labeled bucket. Filter it out and your demographics profile silently loses a whole row and understates the user base.

---

## Common follow-up questions

- How would you show the percentage of total users in each bucket? _(Tests window function: 100.0 * COUNT(*) / SUM(COUNT(*)) OVER ().)_
- What if the threshold was 'more than the average bucket size'? _(Tests subquery-based threshold: HAVING COUNT(*) > (SELECT AVG(cnt) FROM ...).)_
- How would you label the missing-age segment on a dashboard? _(Tests presentation of the null group, e.g. COALESCE(age_bucket, 'Unknown').)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/everyone_counts)
- [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.