Определить, хватает ли подарков на складе для всех доставок, находящихся в статусе "сборка"

Предмет: Базы данных
Раздел: SQL-запросы, агрегация, соединения (JOIN), фильтрация данных

? Задача:

Нужно определить, хватает ли подарков на складе для всех доставок, находящихся в статусе "сборка".
Для этого необходимо:

1. Посчитать требуемое количество подарков по каждому складу и типу подарка.
2. Посчитать доступное количество подарков на складе.
3. Вычислить разницу между доступным и требуемым количеством.
4. Отсортировать результат от меньшего к большему (то есть сначала покажем те, где нехватка или на грани).

? Объяснение структуры:

• Таблица deliveries содержит статус доставки и warehouse_id, где находится подарок.
• Таблица letters указывает, какой подарок (gift_id) требуется.
• Таблица stocks показывает, сколько подарков есть на складе (warehouse_id, gift_id, quantity).

✅ SQL-запрос:

SELECT 
    s.warehouse_id,
    s.gift_id,
    COALESCE(s.quantity, 0) AS available_quantity,
    COALESCE(r.required_quantity, 0) AS required_quantity,
    COALESCE(s.quantity, 0) - COALESCE(r.required_quantity, 0) AS difference
FROM stocks s
LEFT JOIN (
    SELECT 
        d.warehouse_id,
        l.gift_id,
        COUNT(*) AS required_quantity
    FROM deliveries d
    JOIN letters l ON d.letter_id = l.letter_id
    WHERE d.delivery_status = 'сборка'
    GROUP BY d.warehouse_id, l.gift_id
) r ON s.warehouse_id = r.warehouse_id AND s.gift_id = r.gift_id
ORDER BY difference ASC;

? Пояснение:

• В подзапросе r мы считаем, сколько подарков требуется на каждом складе, фильтруя по статусу сборка.
• Основной запрос соединяет stocks с этим подзапросом по складу и подарку.
• Используем COALESCE, чтобы заменить NULL на 0 (если нет записей в одной из таблиц).
• Вычисляем разницу между доступным и требуемым количеством.
• Сортируем по этой разнице, чтобы в начале были те, где нехватка.

? Пример результата:

Если в таблице stocks отсутствует комбинация warehouse_id и gift_id, которая есть в deliveries, то такие случаи можно также включить, используя FULL OUTER JOIN (если поддерживается), либо объединить с подзапросом через UNION.

Нужна версия с учетом этого?