Where in the World Are Our Customers?

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

Is our customer base really concentrated in one country, or is it spread out? Give each country's share of all rows in the customers table as a decimal percentage, biggest share first.

One country dominates the logo wall. Or does it?

Tables

users

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

customers

Columns: customer_id INTEGER, first_name TEXT, last_name TEXT, country TEXT

Expected output

Worked solution and explanation

What this really is

Strip the planning framing and this is a share of total: each country's slice of every row in customers. Anyone can count per country. What separates people is the arithmetic around the denominator. The total is one number over the whole table, and both it and COUNT(*) are integers, so dividing them is integer division on this engine. With 200 rows every true share is a multiple of 0.5, so the trap does not blank the answer. It quietly shaves the half off every odd count: Australia reads 3 instead of 3.5, and the result still looks perfectly plausible.

Two ways the arithmetic lies

Integer division

COUNT(*) * 100 / (SELECT COUNT(*) FROM customers): Australia's 7 rows give 700 / 200, which truncates to 3. Every odd count loses its half percent.

Fractional division

CAST(COUNT(*) AS REAL) * 100 / (SELECT COUNT(*) FROM customers): a real numerator makes the whole expression real, so the same 7 rows give 3.5.

Building it

Step 1: Count rows per country

Grouping customers by country with COUNT(*) gives the size of each slice. Count rows, not unique customer_id values: a customer who moved is listed once per country, and the preview's 4.0 for 8 rows only works out against all 200 rows.

Step 2: Divide by the grand total

The denominator is the count over the entire table, so it lives in the uncorrelated scalar subquery (SELECT COUNT(*) FROM customers). Nothing ties it to the outer group, so it is one number shared by every row of the result.

Step 3: Convert before you divide, then sort

Wrap the count in CAST(... AS REAL) before the division so the fraction survives, alias the result share_pct, and sort by it descending so the largest slice leads. Countries that tie on share may come back in either order; the question only asks for biggest first.

SELECT
  country,
  CAST(COUNT(*) AS REAL) * 100 / (SELECT COUNT(*) FROM customers) AS share_pct
FROM customers
GROUP BY country
ORDER BY share_pct DESC;

Common follow-up questions

• How would you also include countries that currently have zero customers, given a separate countries reference table? (Tests an outer join and counting a nullable column instead of `COUNT(*)`.)
• Rewrite it so the grand total comes from a window over the grouped rows instead of a subquery. (Tests `SUM(COUNT(*)) OVER ()` and how an aggregate over groups is evaluated.)
• The team only wants countries above a 5% share. Where does that filter go? (Tests filtering on a computed aggregate in `HAVING` rather than `WHERE`.)