Как составить запрос?

Question

[Андрей Беспалов]

Есть база вида:

Нужен вот такой запрос:
для всех дилерских центров те модели (mark.name), для которых суммарное количество выставленных на продажу экземпляров больше 10 (stock.quantity). В этом же запросе для каждой модели указать центр (shop.name), в котором находится наибольшее количество моделей (если таких несколько, то отсортировать по имени центра в алфавитном порядке).
Основной запрос понятно как сделать:

SELECT
	mark.name,			
	SUM(stock.quantity)			
FROM
	stock
	LEFT JOIN mark ON mark.id=stock.markid				
GROUP BY
	mark.name			
HAVING	SUM(stock.quantity)>10

А вот как присобачить к каждой модели машины имя дилерского центра в котором наибольшее количество моделей, не понимаю((

Comments

[Akina]

А если просто надо было бы получить

имя дилерского центра в котором наибольшее количество моделей

справился бы?

[Андрей Беспалов]

Ну тут я так понял не просто один и тот же дилерский центр с наибольшим количеством моделей, а для каждой модели отдельно, т.е дилерский центр с наибольшим количеством машин этой модели на стоке. Т.е это какой-то внутренний запрос, который находит наибольшее значение машин конкретной модели на стоке, связывает это с салоном и возвращает во внешний запрос.

[Akina]

Начни с простого связывания таблиц. Это должно выглядеть как-то так:

SELECT *
FROM stock
JOIN shop USING (shop_id)
JOIN mark USING (mark_id)
JOIN manufacturer USING (manufacturer_id);

Дальше количество моделей (вообще хрен понять, где тут "модель" - будем считать, что это mark) в каждом отдельном центре легко считается обычной оконной функцией в CTE.

WITH cte AS (
SELECT *, COUNT(DISTNCT mark_id) OVER (PARTITION BY stock_id) marks_per_stock
FROM stock
JOIN shop USING (shop_id)
JOIN mark USING (mark_id)
JOIN manufacturer USING (manufacturer_id)
)

Сортировка по количеству моделей (посчитанному marks_per_stock) делается оконной функцией RANK() или DENSE_RANK() (учитываем, что количество не обязано быть уникальным, поэтому ROW_NUMBER() не пойдёт) во втором CTE.

Ну а снаружи осталось отобрать то, что проходит под условие "10 штук" и имеет посчитанный номер, равный 1.

PS. На самом деле таблица manufacturer в этом супе нафиг не нужна.

PPS. Вот fiddle. Можете заполнить данными (если надо, откорректировав структуры), показать нужный ответ для этих данных, ну и вообще поиграться.