Each country's best customer challenge

I think my sql is cuter than the official answer…here it is!.

With Top_customers 
AS
(
SELECT  
C.country,C.first_name || C.last_name as customer_name,
(SELECT sum(L.Unit_price*Quantity) from invoice_line as L
  INNER join invoice as a on a.invoice_id=L.invoice_id
where a.customer_id=c.customer_id)as total_purchased

from customer as C

    ORDER by 3 desc
)
SELECT country,customer_name,round(MAX(total_purchased),2)  from Top_customers
GROUP BY country
ORDER by 1
2 Likes

Hey @ronald.kamulegeya.20. nice work!

I want to quickly draw your attention to something that could trip you up with databases other than SQLite. In your outermost query you have a variable, customer_name, which is neither in an aggregate function nor listed in the group by.

In a lot of SQL variants this query would fail with an error, because it’s not clear what the database should do in this instance. SQLite happens to be an exception to this rule.

If you’d like to know more, I wrote about this in more detail just earlier today:

1 Like