Intermediate Joins · JOIN + GROUP BY
14. Revenue by country
Concept: JOIN + GROUP BYshow
You can aggregate across a join: join first so every row has the columns you need, then GROUP BY a column from either table.
Example (different data):
SELECT a.country, COUNT(*) AS books FROM books b JOIN authors a ON a.id = b.author_id GROUP BY a.country;
Your task
The board deck needs completed revenue by customer country. Country lives on customers; revenue lives on orders.
Return country and total_revenue (sum of completed order amounts), highest total_revenue first.
Solution unlocks after 3 more tries
Starting Postgres in your browser…
query.sql
Output
Write a query and press Run Query (Ctrl/⌘ + Enter).