Write a query which will return all cities more customers than the average number of customer of all cities. For each such city, return the country name, the city name and the number of customers. Order the result by country name ascending.

Table definitions and a data sample are given below.

Schema

Table: country

column name column type key / NULL
id int PK
country_name varchar(128)

Table: city

column name column type key / NULL
id int PK
city_name varchar(128)
postal_code varchar(16)
country_id int FK

city.country_id references country.id

Table: customer

column name column type key / NULL
id int PK
customer_name varchar(255)
city_id int FK
customer_address varchar(255) FK
contact_person varchar(255) N
email varchar(128)
phone varchar(128)

customer.city_id references city.id

Sample Data Tables

Table: country

id country_name
1 Austria
2 Germany
3 United Kingdom

Table: City

id city_name postal_code country_id
1 Wien 1010 1
2 Berlin 10115 2
3 Hamburg 20095 2
4 London EC4V 4AD 3

Table: Customer

id customer_name city_id customer_address contact_person email Phone
1 Drogerie Wien 1 Deckergasse 15A Emil Steinbach emil@drogeriewein.com 094234234
2 Costemitc Store 4 Watling Street 347 Jeremy Corbyn jeremy@c-store.org 093923923
3 Kosmetikstudio 3 Rothenbaumchaussee 53 Willy Brandt willy@kosmetikstudio.com 0941562222
4 Neue Kosmetik 1 Karlspatz 2 NULL info@neuekosmetik.com 094109253
5 Bio Kosmetik 2 Motzrabe 23 Clara Zetkin clara@biokomsetik.org 093825825
6 K-Wien 1 Karnter Strabe 204 Maria Rauch-Kallat maria@kwien.org 093427002
7 Natural Cosmetics 4 Clerkenwell Road 14B Glenda Jackson glen.j@natural-cosmetics.com 093555123
8 Kosmetik Plus 2 Unter den Linden 1 Angela Merkel angela@k-plus.com 094727727
9 New Line Cosmetics 4 Devonshire Street 92 Oliver Cromwell oliver@nlc.org 093202404

The first line of the result should be:

counry_name city_name count
-> Austria Wien 3
Academic Honesty!
It is not our intention to break the school's academic policy. Posted solutions are meant to be used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly complete the form. Please provide a valid email address and we will get back to you within 24 hours. Payment is through PayPal, Buy me a Coffee or Cryptocurrency. We are a nonprofit organization however we need funds to keep this organization operating and to be able to complete our research and development projects.