Топ-10 городов по числу клиентов

Задача

Отдел развития выбирает города под офлайн-акции: «Где у нас больше всего клиентов? Дай топ-10 городов по числу клиентов».

Верни название города и число клиентов в нём — первые десять, от самого крупного.

Схема данных

Учебная база проката dvdrental. Город клиента хранится не напрямую, а через адрес:

• customer(customer_id, first_name, last_name, address_id, ...) — клиенты; у клиента есть address_id.
• address(address_id, city_id, ...) — адреса; адрес ссылается на город.
• city(city_id, city, ...) — справочник городов; city — название.

Решение

SELECT ci.city,
       count(*) AS customers
FROM customer cu
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
GROUP BY ci.city
ORDER BY customers DESC, ci.city
LIMIT 10;

Разбор

У клиента нет поля «город» — есть только address_id. Чтобы добраться до названия города, идём по цепочке: клиент → адрес (по address_id) → город (по city_id). Два JOIN, каждый по своему ключу. USING (address_id) и USING (city_id) — короткая запись соединения, когда ключ называется одинаково в обеих таблицах.

Когда цепочка собрана, GROUP BY ci.city складывает клиентов по городам, а COUNT(*) считает, сколько их в каждом. Здесь COUNT(*) уместнее, чем COUNT(cu.customer_id): после INNER JOIN у каждого клиента гарантированно есть адрес и город, лишних строк соединение не породило, так что считать строки группы — то же самое, что считать клиентов.

Небольшая, но важная деталь — ORDER BY customers DESC, ci.city. Первый ключ сортировки очевиден: по числу клиентов, по убыванию. А вот второй, ci.city, — страховка от неоднозначности: если у нескольких городов одинаковое число клиентов, без второго ключа порядок между ними не определён, и LIMIT 10 может каждый раз обрезать список по-разному. Добавив сортировку по названию, мы делаем вывод стабильным и воспроизводимым — на собеседовании за такую аккуратность с LIMIT ставят плюс.

LIMIT 10 берёт только первую десятку — ровно то, что просил отдел развития.

Если бы вопрос был не «сколько клиентов», а «сколько денег принёс каждый город», к этой цепочке добавились бы ещё платежи — как в задаче про выручку по категориям, где JOIN тянется через полбазы. Про то, как соединения могут менять число строк, полезно помнить из вопроса может ли LEFT JOIN вернуть больше строк.

Ожидаемый результат

city | customers
--- | ---
Aurora | 2
London | 2
A Corua (La Corua) | 1
Abha | 1
Abu Dhabi | 1
Acua | 1
Adana | 1
Addis Abeba | 1
Aden | 1
Adoni | 1