SQL Fundamentals - SQL Subqueries - Multi-row and Multi-column Subqueries in SQL - Find average number of sales per country per customer

Fundamentals of SQL > SQL Subqueries > Multi-row and Multi-column Subqueries in SQL > Screen 6 > Find the average number of sales per country per customer
Question: Find average number of sales per country per customer.

My Code:

``````SELECT x.billing_country, AVG(no_sales_country) AS sale_tally
FROM customer c
JOIN (SELECT customer_id, billing_country, COUNT(*) AS                               no_sales_country
FROM invoice
GROUP BY billing_country) AS x
ON c.customer_id = x.customer_id
GROUP BY c.customer_id
ORDER BY no_sales_country DESC

``````

What I expected to happen:

The expected output is:

Country sale_avg_tally
Czech Republic 15
Portugal 14
Chile 13
Ireland 13
Brazil 12
Finland 11
Spain 11
Australia 10
Denmark 10
France 10

What actually happened:

``````billing_country 	sale_tally
USA		       131
Brazil			61
France			50
Germany		41
CzechRepublic	30
Portugal		29
UnitedKingdom	28
India			21
Chile			13
``````

Please help to explain why the code produces an incorrect output. First in the nested subquery on JOIN statement, number of sales per country is calculated. Then average of that is calculated in the outer subquery based on per customer. GROUP BY clause is used for grouping the rows first by country (nested subquery) and then by customers.

I am stuck on the same question. To get the output which you are saying as expected:

``````SELECT ct.country AS country,  i.invoice_tally / ct.customer_tally AS sale_avg_tally
FROM
(SELECT billing_country, COUNT(*) AS invoice_tally
FROM invoice
GROUP BY billing_country) AS i
JOIN
(SELECT country, COUNT(*) AS customer_tally
FROM customer
GROUP BY country) AS ct
ON i.billing_country = ct.country
GROUP BY ct.country
ORDER BY sale_avg_tally DESC;
``````

However, it doesnâ€™t still validate the output as the correct one.

You have to order by country as well