# Where in the World Are Our Customers? > One country dominates the logo wall. Or does it? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The go-to-market team keeps insisting the customer base is concentrated in a single country, and we want the real spread before the next planning cycle. Break the customers down by country and give each country's percentage of the whole, biggest share first. ## Worked solution and explanation ### What this really is Strip the business framing and this is a share-of-total: each country's slice of the whole customer base. Anyone can group by country and count the rows. The part that separates people is the denominator and the arithmetic around it. The total is one number over every customer, not the count inside each group, and if you divide two integers the engine throws away the fraction and hands you 0.00 for every country below a full 100%. Both mistakes return a result that looks plausible and is completely wrong. ### The two ways to get zero > **The integer-division trap** > > COUNT(*) is an integer and (SELECT COUNT(*) FROM customers) is an integer. In SQLite, an integer divided by an integer is integer division: 5 / 20 evaluates to 0, not 0.25. Multiply by 100.0 or wrap the numerator in CAST(... AS REAL) first, so the division happens in floating point. Forget it and every share comes back 0.00, which is the single most common way this problem is failed. **Looks right, returns zeros** COUNT(*) * 100 / (SELECT COUNT(*) FROM customers): the numerator is still an integer product, so integer division truncates and every share under 100% collapses to 0. **Divides in floating point** CAST(COUNT(*) AS REAL) * 100.0 / (SELECT COUNT(*) FROM customers): a real numerator forces real division, so 5 out of 20 becomes 25.0 as intended. ### Building it #### Step 1: Count customers per country GROUP BY country with COUNT(*) gives the raw size of each slice. This is the easy half, and everyone gets here. #### Step 2: Divide by the grand total, once The denominator is the count over the entire table, so it lives in a scalar subquery: (SELECT COUNT(*) FROM customers). It has no link to the outer grouping, so it is evaluated a single time, which is exactly what a share-of-total needs. #### Step 3: Force floating point, then round Cast the numerator to REAL (or multiply by 100.0) before dividing, otherwise integer division zeroes everything out. Wrap the result in ROUND(..., 2) for a clean two-decimal percentage, then sort by that share descending with an alphabetical tie-break so equal shares come out in a stable order. **Share of customers by country** ```sql SELECT country, ROUND(CAST(COUNT(*) AS REAL) * 100.0 / (SELECT COUNT(*) FROM customers), 2) AS share_pct FROM customers GROUP BY country ORDER BY share_pct DESC, country; ``` *Per-group count over a single grand total, computed in floating point.* > **What the interviewer is watching** > > Two tells. First, do you reach for floating-point division without being reminded, or do you ship a query that silently returns all zeros? Second, do you make the ordering deterministic? Sorting by share descending alone leaves the countries tied on the same share (France and Germany both at 15%) in undefined order; adding the country tie-break shows you think about reproducible output. > **The subquery runs once, not per row** > > An uncorrelated scalar subquery is evaluated a single time, so (SELECT COUNT(*) FROM customers) does not re-scan the table for every group. The alternative, SUM(COUNT(*)) OVER (), gets the same grand total in one pass with a window over the grouped rows; both are cheap here, and the window form avoids naming the table twice. ## Common follow-up questions - How would you also include countries that currently have zero customers, given a separate countries reference table? _(Tests LEFT JOIN and the difference between COUNT(*) and COUNT of a nullable column.)_ - Rewrite it so the grand total comes from a window function instead of a subquery. _(Tests SUM(COUNT(*)) OVER () and understanding aggregate-over-group.)_ - The team only wants countries above a 5% share. Where does that filter go? _(Tests filtering on a computed aggregate and why it belongs in HAVING, not WHERE.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_in_the_world_are_our_customers) - [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.