Product Sales Per City

This question comes up a lot in the HackerRank SQL (Intermediate) test. I got it in my own attempt in June 2022. It is mostly a joins question: five tables, one SUM, and an ordering rule with two tie-breakers.

What the question asks

For every city and every product sold in that city, find the total amount customers from that city spent on that product. Round the total to 2 decimal places.

Show the city name, the product name and the total. Sort by:

1. the total, highest first
2. then city name, A to Z
3. then product name, A to Z

Tables

One invoice has many invoice items. Each item is one product on that invoice, and line_total_price is what the customer paid for that line.

Solution

SELECT
    ci.city_name,
    pr.product_name,
    ROUND(SUM(ii.line_total_price), 2) AS total_spent
FROM city AS ci
JOIN customer AS cu ON cu.city_id = ci.id
JOIN invoice AS i ON i.customer_id = cu.id
JOIN invoice_item AS ii ON ii.invoice_id = i.id
JOIN product AS pr ON pr.id = ii.product_id
GROUP BY ci.city_name, pr.product_name
ORDER BY total_spent DESC, ci.city_name ASC, pr.product_name ASC;

How it works

1. Join from city down to the line items. City to customer, customer to invoice, invoice to invoice item, and invoice item to product. After that, every row is one invoice line with its city name and product name attached.
2. Group by city and product. Each group holds all the lines for one product bought by customers of one city, across every customer and every invoice. SUM(ii.line_total_price) adds them up.
3. Round. ROUND(..., 2) gives 2 decimal places. In MySQL the column is decimal(8,2), so the sum already has 2 decimals and the round is a safety net. It matters in databases that use floating point.
4. Sort with tie-breakers. Two cities can spend exactly the same amount on a product, and one city can spend the same amount on two products. The second and third sort keys decide the order in those cases.

Use line_total_price, not invoice.total_price. The invoice total covers every product on the invoice, so it cannot be split by product.

Mistakes that fail the hidden tests

• Leaving out the tie-breakers. ORDER BY total_spent DESC alone gives the right rows, but rows with equal totals can come out in any order. In my sample data it happened to come out right, which is exactly why it is easy to miss. The hidden data has more rows and more ties.
• Not grouping at all. One popular repo has a version that just lists every invoice line with its city and product. That gives one row per line, not one row per city and product.
• Summing invoice.total_price. If an invoice has three products, each of the three rows carries the full invoice total, so the sum is counted three times.
• Using LEFT JOIN from city. Cities with no sales would then appear with a NULL product and a NULL total. The question only wants products that were sold, so inner joins are right here.
• Sorting by the wrong direction. Total is high to low. City and product are A to Z. Writing DESC once at the end does not apply to the earlier columns, and writing it on all three is wrong too.

Try it on sample data

Sample rows I used (only the columns that matter). The edge cases: Berlin and Lyon both spent exactly 500.00 on Cotton Kurta, Pune spent 300.00 each on two different products, and Berlin’s Masala Chai comes from two different customers.

city

customer

invoice

product

invoice_item

Expected output

Check a few by hand: Berlin’s Steel Tiffin Box is 1200.00 + 300.00 from two invoices of Kraus GmbH. Berlin’s Masala Chai is 79.99 (Kraus GmbH) + 120.01 (Delta Supplies). The 500.00 tie is broken by city (Berlin before Lyon) and the 300.00 tie in Pune by product name (Masala Chai before Steel Tiffin Box). Brass Diya Set and Copper Bottle were never sold, so they do not appear.