Invoices Per Country

This is one of the questions in the HackerRank SQL (Intermediate) certification test. It was one of the two I got when I took the test in June 2022. The query below is tested on MySQL 8.0 and 5.7.

What the question asks

Every invoice belongs to a customer, every customer lives in a city, and every city is in a country. You have to work out the average invoice amount for each country and keep only the countries where that average is higher than the average of all invoices in the table.

For each country that qualifies, show three things:

• the country name
• how many invoices came from that country
• the average invoice amount for that country

I could not find a public copy of the full problem text for this one. The task above comes from the solutions in about a dozen GitHub repos (several are copies of each other, but the working ones all have the same shape) plus a code comment in one repo that describes the task. None of them sorts the output or asks for a specific rounding. If your version of the question gives an order, add an ORDER BY for it at the end.

Tables

Four tables are involved. Only the columns you need are listed here.

Solution

SELECT
    co.country_name,
    COUNT(i.id) AS invoice_count,
    ROUND(AVG(i.total_price), 6) AS average_invoice
FROM country AS co
JOIN city AS ci ON ci.country_id = co.id
JOIN customer AS cu ON cu.city_id = ci.id
JOIN invoice AS i ON i.customer_id = cu.id
GROUP BY co.country_name
HAVING AVG(i.total_price) > (SELECT AVG(total_price) FROM invoice);

How it works

1. Walk the chain of joins. An invoice does not store a country. You get there in three steps: invoice to customer, customer to city, city to country. After the joins, every invoice row also carries its country name.
2. Group by country. GROUP BY co.country_name turns all invoice rows of one country into a single row. COUNT(i.id) counts the invoices in that group and AVG(i.total_price) gives the country’s average invoice.
3. Compare with the overall average. The subquery (SELECT AVG(total_price) FROM invoice) runs once and returns the average of every invoice in the table. HAVING keeps only the countries whose own average is strictly higher.
4. Six decimals. In MySQL, AVG of a decimal(8,2) column already comes back with 6 decimal places (for example 1250.000000). The ROUND(..., 6) does not change the value in MySQL. I keep it so the output looks the same if you test the query somewhere else, such as SQLite, where averages are floating point.

We use HAVING and not WHERE because the condition is on an aggregate. WHERE filters single rows before grouping, so it cannot see a country’s average.

Mistakes that fail the hidden tests

• Copying the broken query that is all over GitHub. A query with GROUP BY co.country name and SELECT AVG(total price) (spaces instead of underscores) has been copied into many repos, including my own old one. MySQL rejects it with error 1064. The subquery also has no FROM invoice. Use the query above.
• Using >= instead of >. The task wants countries above the average. In my sample data Brazil’s average is exactly the overall average (800.00). With >= it shows up in the result, with > it does not.
• Putting the average check in WHERE. WHERE AVG(...) > ... is an error. Aggregates go in HAVING.
• Comparing with the average of the country averages. The benchmark is the average of all invoices, one number from the invoice table. The average of the per-country averages is a different number whenever countries have different invoice counts.
• Counting customers instead of invoices. COUNT(DISTINCT cu.id) gives the number of customers. The question wants the number of invoices, so count invoice rows.
• Joining in the wrong direction. ci.country_id = co.id and cu.city_id = ci.id. If you join cu.id = ci.id by mistake, MySQL will still run the query and give you wrong numbers.

Try it on sample data

I built the tables with these rows and ran the query on MySQL 8.0, MySQL 5.7 and SQLite. The edge cases: Brazil sits exactly on the overall average, Japan has a city but no invoices, and Germany’s average has a long decimal.

country

city

customer

invoice

The 11 invoices add up to 8800.00, so the overall average is 800.00. Per country: India 475.998 (5 invoices), Germany 1040.003333 (3), Brazil 800.00 (1), France 1250.00 (2). Japan has no invoices, so the inner joins drop it.

Expected output

This is the MySQL 8.0 output. MySQL 5.7 printed the same two rows with France first, because 5.7 sorts GROUP BY results and 8.0 does not. Same rows, different order, which is why I said above to add an ORDER BY if your version of the question asks for one.