What's in a Name

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

Problem

Find how many usernames in users begin with each letter and what percentage of all users each letter accounts for, most common letters first. Some usernames were saved with a stray leading space or a capital first letter, and those users still belong under their letter.

The first letter is a fingerprint. Read the crowd by it.

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_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

Expected output

First 5 of 26 rows
initialuser_countpct
a178.5
m147
c136.5
s126
j126

Worked solution and explanation

What this really is

This is a share-of-total problem wearing a head-count costume. Counting users per letter is the easy half. Once you group, each row only knows its own letter's count, and the percentage needs the count of every user. Bring the grand total into the grouped result and divide in decimal arithmetic. Divide by the group's own count and every letter reads 100. Divide two integers and 17 out of 200 truncates to 8, or to 0 if the division runs before the multiply.

Build it

Step 1: Clean the initial before you use it

username was not cleaned on entry: ' amelia' carries a leading space and 'Alice' a capital. SUBSTR(username, 1, 1) alone gives them their own rows, ' ' and 'A'. LOWER(SUBSTR(TRIM(username), 1, 1)) trims, takes the first character and lowercases it, so both land under 'a'.

Step 2: Group on the same expression

Repeat the full expression in GROUP BY. Some engines accept the initial alias there, but the expression form runs everywhere and makes it plain that the grain is the cleaned letter, not the raw column.

Step 3: Bring in the grand total

(SELECT COUNT(*) FROM users) returns one number, and every group row reads it. Multiplying by 100.0 first makes the arithmetic decimal, so 17 of 200 comes out as 8.5, not 8.

Step 4: Most common first

ORDER BY user_count DESC is the whole ordering. Letters with equal counts, like 'j' and 's' at 12, may come back either way round, and both orders are correct.

Scalar subquery for the denominator
SELECT
    LOWER(SUBSTR(TRIM(username), 1, 1)) AS initial,
    COUNT(*) AS user_count,
    100.0 * COUNT(*) / (SELECT COUNT(*) FROM users) AS pct
FROM users
GROUP BY LOWER(SUBSTR(TRIM(username), 1, 1))
ORDER BY user_count DESC
Integer arithmetic: every share is 0
SELECT
    LOWER(SUBSTR(TRIM(username), 1, 1)) AS initial,
    COUNT(*) AS user_count,
    COUNT(*) / (SELECT COUNT(*) FROM users) * 100 AS pct
FROM users
GROUP BY LOWER(SUBSTR(TRIM(username), 1, 1))
ORDER BY user_count DESC

COUNT(*) / (SELECT COUNT(*) FROM users) is 0 for every letter before the * 100 ever runs.

Same result with a window over the groups
SELECT
    LOWER(SUBSTR(TRIM(username), 1, 1)) AS initial,
    COUNT(*) AS user_count,
    100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct
FROM users
GROUP BY LOWER(SUBSTR(TRIM(username), 1, 1))
ORDER BY user_count DESC

SUM(COUNT(*)) OVER () adds up the 26 grouped counts instead of rereading users.

Common follow-up questions