User Roster

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: Tata Consultancy Services

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 the largest group to the smallest, with equally sized groups ordered alphabetically by status name.

Which account states are bleeding users?

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_id	username	email	signup_date	account_status	age_bucket
100	alice	alice@example.com	2025-02-02	inactive	25-34
197	aaron42	aaron42@example.com	2026-03-03	suspended	35-44
294	amelia	amelia@example.com	2024-04-04	pending_verification	45-54
391	arjun	arjun@example.com	2025-05-05	active	55-64
488	ava99	ava99@example.com	2026-06-06	inactive	65+
585	andrew_k	andrew_k@example.com	2024-07-07	suspended	NULL
682	anika	anika@example.com	2025-08-08 16:34:00	pending_verification	18-24
779	aiden	aiden@example.com	2026-09-09 14:53:00	active	25-34
876	aria_b	aria_b@example.com	2024-10-10	inactive	35-44
973	brian	brian@example.com	2025-11-11	suspended	45-54

Expected output

First 4 of 4 rows
account_status	user_count	share_pct
active	50	25
inactive	50	25
pending_verification	50	25
suspended	50	25

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

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, account_status

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