Posts tagged as “code challege”

Recently I took a SQL code challenge:

Question:

There are three tables

• country_table: id(int), country_name(string)
• city_table: id(int), city_name(string), postal_code(int), country_id(int)
• customer_table: id(int), customer_name(string), city_id(id), customer_address(string)

Return all cities with more customer than average number of customers of all cities. For each such city, return the country name, the city name, the number of customers.

Output should be

country_name, city_name, count

Idea:

• Use subquery to get the average number of customers of all cities
• Add primary key columns in the group by clause, in order to handle possible cities / countries having the same name
• table aliases make the query easier to write

My Solution:

Select co.country_name, ci.city_name, count(*) no_customers
from city ci
inner join country co on co.id = ci. country_id
inner join customer cu on cu.city_id = ci.id
group by co.country_id, co.country_name, ci.city_id, ci.city_name
having count(*) > (
    select count(*) / count(distinct cu.city_id) from customer cu
)