✅ Решения: Summary session 5

Решение 1

SELECT AVG(standard_cost) AS avg_cost,
       MIN(standard_cost) AS min_cost,
       MAX(standard_cost) AS max_cost,
       SUM(standard_cost) AS total_cost
FROM products;

Решение 2

SELECT category, COUNT(id) AS product_count
FROM products
GROUP BY category
HAVING COUNT(id) >= 3
ORDER BY product_count DESC;

Решение 3

SELECT supplier_ids, category, AVG(standard_cost) AS avg_cost
FROM products
GROUP BY supplier_ids, category
ORDER BY avg_cost DESC;

Решение 4

SELECT COUNT(id) AS missing_reorder
FROM products
WHERE minimum_reorder_quantity IS NULL;

Решение 5

SELECT COUNT(DISTINCT category) AS unique_categories
FROM products;

Решение 6

SELECT CASE
         WHEN reorder_level < 10 THEN 'low'
         WHEN reorder_level BETWEEN 10 AND 20 THEN 'medium'
         ELSE 'high'
       END AS stock_level,
       AVG(list_price) AS avg_price,
       MIN(list_price) AS min_price,
       MAX(list_price) AS max_price
FROM products
GROUP BY stock_level
ORDER BY avg_price DESC;

Решение 7

SELECT AVG(standard_cost) AS avg_box_cost
FROM products
WHERE LOWER(quantity_per_unit) LIKE '%box%';

Решение 8

SELECT category,
       SUM(list_price - standard_cost) AS total_profit
FROM products
WHERE target_level > 40
GROUP BY category
ORDER BY total_profit DESC;