---

-- 6. Сотрудники, у которых не менее 5 территорий

SELECT employee_id, COUNT(*) AS territory_count
FROM employee_territories
GROUP BY employee_id
HAVING COUNT(*) >= 5
ORDER BY territory_count DESC, employee_id;


-- 7. Товары, которые встречаются не менее 25 раз,
-- и среднее количество которых больше 20

SELECT product_id,
       COUNT(*) AS row_count,
       AVG(quantity) AS average_quantity
FROM order_details
GROUP BY product_id
HAVING COUNT(*) >= 25
   AND AVG(quantity) > 20
ORDER BY row_count DESC, average_quantity DESC;


-- 8. Общий список компаний из shippers и suppliers

SELECT company_name
FROM shippers

UNION

SELECT company_name
FROM suppliers

ORDER BY company_name;


-- 9. Города сотрудников, которые встречаются среди территорий

SELECT city
FROM employees
INTERSECT
SELECT territory_description
FROM territories
ORDER BY city;


-- 10. Территории, которых нет среди городов сотрудников

SELECT territory_description
FROM territories

EXCEPT

SELECT city
FROM employees

ORDER BY territory_description;


-- 11. Общий список телефонов без NULL и повторов

SELECT phone
FROM shippers
WHERE phone IS NOT NULL

UNION

SELECT phone
FROM suppliers
WHERE phone IS NOT NULL

ORDER BY phone;


-- 12. Заказы с минимум 4 позициями
-- и средней скидкой больше 0.10

SELECT order_id,
       COUNT(*) AS position_count,
       AVG(discount) AS average_discount
FROM order_details
GROUP BY order_id
HAVING COUNT(*) >= 4
   AND AVG(discount) > 0.10
ORDER BY average_discount DESC, order_id;