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

Q&A DQ Courses
, , ,
#1

Screen Link:

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
Canada		76
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.

Thanks in advance.

#2

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.

#3

You have to order by country as well