Junior

Report for a logistics company You are an analyst at a logistics company that keeps track of warehouse operations. You need to compile a report on the efficiency of each warehouse. For each warehouse, calculate: • total number of operations (count_operations); • total quantity of goods processed at the warehouse (sum_quantity); • average processing time of an operation (avg_processing_time), considering only operations with a specified time (not NULL), rounded to the nearest whole number; • maximum and minimum number of goods processed in a single operation (max_quantity, min_quantity); • number of operations of each type ('delivery', 'shipment', 'transfer') in separate columns: supply_operations, shipment_operations, transfer_operations. Filter warehouses where total number of operations is more than 2 and average processing time does not exceed 60 minutes. Sort the result by warehouse ID in ascending order. Input format Table operations: • operation_id (int) — unique operation identifier • warehouse_id (int) — warehouse identifier • operation_type (text) — type of operation: 'delivery', 'shipment', 'transfer' • quantity (int) — number of goods units in the operation • operation_date (timestamp) — date and time of the operation • processing_time (int) — processing time of the operation The processing_time column may contain nulls. Output format The query should return a table with fields in the following order: • warehouse_id (int) — warehouse identifier • count_operations (int) — total number of operations at the warehouse • sum_quantity (int) — total quantity of goods processed at the warehouse • avg_processing_time (numeric) — average processing time of operations (in minutes), considering only non-NULL times, rounded to the nearest whole number • max_quantity (int) — maximum number of goods processed in a single operation • min_quantity (int) — minimum number of goods processed in a single operation • supply_operations (int) — number of 'delivery' operations • shipment_operations (int) — number of 'shipment' operations • transfer_operations (int) — number of 'transfer' operations.

sobes.tech AI

Answer from AI

sql SELECT warehouse_id, COUNT() AS count_operations, SUM(quantity) AS sum_quantity, ROUND(AVG(processing_time)) AS avg_processing_time, MAX(quantity) AS max_quantity, MIN(quantity) AS min_quantity, SUM(CASE WHEN operation_type = 'поставка' THEN 1 ELSE 0 END) AS supply_operations, SUM(CASE WHEN operation_type = 'отгрузка' THEN 1 ELSE 0 END) AS shipment_operations, SUM(CASE WHEN operation_type = 'перемещение' THEN 1 ELSE 0 END) AS transfer_operations FROM operations WHERE warehouse_id IS NOT NULL GROUP BY warehouse_id HAVING COUNT() > 2 AND (AVG(processing_time) <= 60 OR AVG(processing_time) IS NULL) ORDER BY warehouse_id ASC;


**Пояснения:**
- `COUNT(*)` считает все операции на складе.
- `SUM(quantity)` суммирует количество товаров.
- `AVG(processing_time)` считает среднее время обработки, игнорируя NULL.
- `ROUND` округляет среднее время до целого.
- `MAX` и `MIN` находят максимальное и минимальное количество товаров в одной операции.
- `SUM(CASE WHEN ...)` считает количество операций каждого типа.
- Фильтрация по количеству операций и среднему времени обработки в `HAVING`.
- Сортировка по `warehouse_id` по возрастанию.

Обратите внимание, что `AVG(processing_time)` автоматически игнорирует NULL значения, поэтому не нужно дополнительной фильтрации.

Previous Next