# User Roster

> Which account states are bleeding users?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The growth team is building a retention dashboard and needs to understand the distribution of account states. For each account status, show the number of users and what share of the total user base that status represents. Round the share to one decimal place and list from largest group to smallest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests GROUP BY with a window function for computing both a count and a percentage in a single query. Interviewers check whether you can compute a group's share of the total without a subquery.

---

### Break down the requirements

#### Step 1: Count users per status

`GROUP BY account_status` with `COUNT(*)` produces the count per group.

#### Step 2: Compute share of total

Divide each group's count by the total user count (via a subquery or window function), multiply by 100, and round to 1 decimal place.

#### Step 3: Order by count descending

`ORDER BY user_count DESC` lists from largest group to smallest.

---

### The solution

**Group counts with percentage of total**

```sql
SELECT
    account_status,
    COUNT(*) AS user_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM users), 1) AS share_pct
FROM users
GROUP BY account_status
ORDER BY user_count DESC
```

> **Cost Analysis**
>
> Two scans of 1M rows: one for the GROUP BY and one for the scalar subquery. The scalar subquery executes once and is cached. With 5 account statuses, the aggregation is trivial.

> **Interviewers Watch For**
>
> Whether you use a scalar subquery or a window function for the total. Both are correct. A window function approach would be `COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()`.

> **Common Pitfall**
>
> Integer division when computing the percentage. `COUNT(*) * 100 / total` truncates to 0 for small groups. Multiply by 100.0 (float) to force decimal division.

---

## Common follow-up questions

- What if the percentages do not sum to exactly 100 due to rounding? _(Tests awareness of rounding error accumulation and potential mitigation strategies.)_
- How would you add a cumulative percentage column? _(Use SUM(share_pct) OVER (ORDER BY user_count DESC) for a running total.)_
- What if account_status had NULL values? _(GROUP BY includes NULL as a group; decide if it should be labeled or excluded.)_

## Related

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