The following querry is an alternative solution to find the customer from each country that has spent the most money at our store. In our database there are no 'ties' for best customer in each country, and we will ignore this case for the exercise.
The querry includes:
WITH
clauseSUM()
and MAX()
INNER JOINs
GROUP BY
and ORDER BY
clausesThe result is ordered alphabetically by country. The query returns the following columns, in order:
country
- The name of each country that we have a customer from.customer_name
- The first_name
and last_name
of the customer from that country with the most total purchases, separated by a space, eg Luke Skywalker
.total_purchased
- The total dollar amount that customer has purchased.%%capture
%load_ext sql
%sql sqlite:///chinook.db
%%sql
WITH
customer_country_purchases AS
(
SELECT * FROM customer
),
total_purchases AS
(
SELECT
inv.customer_id,
SUM(inv.total) total
FROM invoice inv
INNER JOIN customer cu ON inv.customer_id = cu.customer_id
GROUP BY cu.customer_id, cu.country
)
SELECT
ccp.country country,
ccp.first_name || " " || ccp.last_name customer_name,
MAX(tp.total) total_purchased
FROM customer_country_purchases ccp
INNER JOIN total_purchases tp ON
tp.customer_id = ccp.customer_id
GROUP BY country
ORDER BY country;
* sqlite:///chinook.db Done.
country | customer_name | total_purchased |
---|---|---|
Argentina | Diego Gutiérrez | 39.6 |
Australia | Mark Taylor | 81.18 |
Austria | Astrid Gruber | 69.3 |
Belgium | Daan Peeters | 60.38999999999999 |
Brazil | Luís Gonçalves | 108.89999999999998 |
Canada | François Tremblay | 99.99 |
Chile | Luis Rojas | 97.02000000000001 |
Czech Republic | František Wichterlová | 144.54000000000002 |
Denmark | Kara Nielsen | 37.61999999999999 |
Finland | Terhi Hämäläinen | 79.2 |
France | Wyatt Girard | 99.99 |
Germany | Fynn Zimmermann | 94.05000000000001 |
Hungary | Ladislav Kovács | 78.21 |
India | Manoj Pareek | 111.86999999999999 |
Ireland | Hugh O'Reilly | 114.83999999999997 |
Italy | Lucas Mancini | 50.49 |
Netherlands | Johannes Van der Berg | 65.34 |
Norway | Bjørn Hansen | 72.27000000000001 |
Poland | Stanisław Wójcik | 76.22999999999999 |
Portugal | João Fernandes | 102.96000000000001 |
Spain | Enrique Muñoz | 98.01 |
Sweden | Joakim Johansson | 75.24 |
USA | Jack Smith | 98.01 |
United Kingdom | Phil Hughes | 98.01 |