Customer Spending

Customer Spending looks like the easiest question in the HackerRank SQL (Intermediate) test. The logic is short. What trips people up is the output format: the amount has to show 6 decimal places, and the usual tricks for that behave differently from what you expect.

What the question asks

Find the customers who spent very little. A customer qualifies if the total of all their invoices is 25% or less of the average invoice amount (the average over every invoice in the table).

For each such customer, show their name and their total spend with exactly 6 decimal places. Sort by the total spend, highest first.

Tables

Solution

SELECT
    cu.customer_name,
    CAST(SUM(i.total_price) AS DECIMAL(12, 6)) AS amount_spent
FROM customer AS cu
JOIN invoice AS i ON i.customer_id = cu.id
GROUP BY cu.id, cu.customer_name
HAVING SUM(i.total_price) <= 0.25 * (SELECT AVG(total_price) FROM invoice)
ORDER BY amount_spent DESC;

How it works

1. Total per customer. Join customer to invoice, group by customer and SUM(i.total_price). I group by cu.id as well as the name, so two different customers who happen to share a name are not merged.
2. The benchmark. (SELECT AVG(total_price) FROM invoice) is the average of all invoices. Multiply by 0.25 to get the cut-off.
3. Filter the groups. HAVING SUM(...) <= cut-off keeps customers at or below it. The question says “25% or less”, so <= is the literal reading.
4. Six decimals. CAST(... AS DECIMAL(12, 6)) turns 179.99 into 179.990000. 12 digits in total leaves room for 6 before the decimal point.
5. Sort. amount_spent is still a number after the cast, so ORDER BY amount_spent DESC sorts numerically.

Customers with no invoices drop out at the inner join. That is fine: the question is about how much customers spent, and most solutions people report passing with use an inner join.

Most public solutions use < instead of <=, and people report passing with both. That suggests the hidden data has no customer sitting exactly on the cut-off. I go with <= because it matches the wording of the task. If your version of the question says “less than 25%”, switch to <.

Mistakes that fail the hidden tests

• ROUND(SUM(...), 6) on MySQL 8. This is the most copied answer, and it does not do what it looks like. On MySQL 8.0 I got 179.99 and 2000.00, still 2 decimals, because rounding a 2-decimal value to 6 places changes nothing. On MySQL 5.7 the same query printed 179.990000. HackerRank’s current environment page lists MySQL 8.0.33. Use CAST(... AS DECIMAL(12, 6)).
• FORMAT(SUM(...), 6). It gives 6 decimals but adds thousands separators (2,000.000000) and returns text. If you then sort by it, the sort is alphabetical: I tested 99.5, 120.01 and 1500 with ORDER BY ... DESC and got 99.500000 first.
• CAST(... AS DECIMAL(9, 6)). Several repos use this. DECIMAL(9, 6) has only 3 digits before the point, so anything from 1000 up becomes 999.999999 with an “out of range” warning. Give it more room.
• Checking each invoice instead of each customer. WHERE i.total_price <= ... without a GROUP BY lists small invoices, not small customers. In my sample data Bharat Foods then shows up twice (129.99 and 50.00) instead of once with 179.99.
• Using the wrong average. The cut-off is 25% of the average invoice, not 25% of the average customer total. With the sample data the average invoice is 800.00, so the cut-off is 200.00.

Try it on sample data

Sample rows I used. The edge case: Om Electricals spent exactly 200.00, which is exactly the cut-off. Nila Stores and Zen Retail have no invoices.

customer

invoice

The 11 invoices total 8800.00, so the average invoice is 800.00 and the cut-off is 200.00. Customer totals: Asha 2000.00, Bharat 179.99, Kraus 3000.00, Rio 800.00, Delta 120.01, Lumiere 2500.00, Om 200.00.

Expected output

MySQL 8.0 and 5.7 both gave this output. With < instead of <=, Om Electricals drops out and only the last two rows remain. To run it in SQLite, which has no fixed-point decimals, replace the CAST with PRINTF('%.6f', SUM(i.total_price)) and sort by SUM(i.total_price) DESC. That gives the same three rows.