How i can improve this sql query ? Question in describtion

There is 3 table

1- country ---- id,country_name
2- city ------- id,city_name,country_id
3- customer --- customer_name, city_id

and the question is “Write the query that finds the country name, city name, and the number of customers of cities with more customers than the average number of customers in the cities.”

and my solution is

SELECT C.country_name, CT.city_name, 
CNTS.customer_count
FROM country C, city CT, (SELECT CTR.city_id, 
COUNT(CTR.city_id) AS customer_count 
FROM customer CTR
WHERE (SELECT COUNT(city_id) FROM customer WHERE 
city_id = CTR.city_id) > (SELECT COUNT(id) * 1.0 / 
COUNT(DISTINCT(city_id)) FROM customer)
GROUP BY CTR.city_id) CNTS
WHERE CNTS.city_id = CT.id AND C.id = CT.country_id
ORDER BY C.country_name ASC;

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

It helps me to do JOINs, and do them in the order that the Optimizer will probably use. In this case, starting with aggregates and ending with details.

SELECT a.avg1, b.city_id, b.ct, c.*, d.*
    FROM ( SELECT COUNT(*) / COUNT(DISTINCT city_id) AS avg1
                FROM customers ) AS a
    JOIN ( SELECT city_id, COUNT(*) AS ct
                FROM customers GROUP BY city_id ) AS b
         -- no "ON"; see note below
    JOIN city AS c  ON b.city_id = c..id
    JOIN country AS d  ON c.country_id = d.id

The first “derived table” will have one row: a.avg1.

The second will have one row per city: city_id, ct.

Look at the output from that SELECT, and finish up the desired task by changing * to what you need and tacking on a WHERE clause to limit it to certain rows. And and ORDER BY.

(Note: In spite of using the “new” Join syntax, I left out an ON clause because there is only one row in one of the tables.)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0