---

В листинге 35 представлен простой запрос для вывода запчастей с остатками на складе.

Листинг 35

```sql
SELECT 
    part_name,
    manufacturer,
    quantity,
    sale_price,
    purchase_price,
    (sale_price - purchase_price) AS markup
FROM spare_parts
WHERE quantity > 0
ORDER BY quantity ASC, part_name;
```

В листинге 36 представлен простой запрос для вывода платежей по заказам.

Листинг 36

```sql
SELECT 
    p."ID" AS payment_id,
    p."order_ID" AS order_id,
    p.payment_type,
    p.payment_method,
    p.amount,
    p.date_payment
FROM payments p
JOIN work_orders wo ON p."order_ID" = wo.id
ORDER BY p.date_payment DESC;
```

В листинге 37 представлен простой запрос для вывода заказов поставщикам с их статусом.

Листинг 37

```sql
SELECT 
    so.id AS supply_order_id,
    s.supplier_name,
    sp.part_name,
    so.order_date,
    so.expected_date,
    so.status,
    so.quantity
FROM supply_orders so
JOIN suppliers s ON so.supplier_id = s.id
JOIN spare_parts sp ON so.part_id = sp.id
ORDER BY so.status, so.order_date DESC;
```

5.5 Сложные запросы

В листинге 38 представлен сложный запрос для вывода топ-5 самых дорогих выполненных заказов с перечнем оказанных услуг.

Листинг 38

```sql
SELECT 
    wo.id AS order_id,
    c.brand || ' ' || c.model AS car,
    cl.surname || ' ' || LEFT(cl.first_name, 1) || '.' AS client,
    wo.total_cost,
    wo.reception_date,
    wo.completion_date,
    (SELECT STRING_AGG(s.service_name, ', ') 
     FROM order_services os 
     JOIN services s ON os.service_id = s.id 
     WHERE os.order_id = wo.id) AS services_performed
FROM work_orders wo
JOIN cars c ON wo.car_id = c.id
JOIN clients cl ON c.client_id = cl.id
WHERE wo.status = 'Выполнен'
ORDER BY wo.total_cost DESC
LIMIT 5;
```

В листинге 39 представлен сложный запрос для вывода общей прибыли по месяцам за последний год.

Листинг 39

```sql
SELECT 
    EXTRACT(YEAR FROM wo.reception_date) AS year,
    EXTRACT(MONTH FROM wo.reception_date) AS month,
    COUNT(*) AS orders_count,
    SUM(wo.total_cost) AS total_revenue,
    SUM(wo.total_cost) - COALESCE(SUM(op.quantity * sp.purchase_price), 0) AS total_profit
FROM work_orders wo
LEFT JOIN order_parts op ON wo.id = op.order_id
LEFT JOIN spare_parts sp ON op.part_id = sp.id
WHERE wo.reception_date >= CURRENT_DATE - INTERVAL '1 year'
    AND wo.status = 'Выполнен'
GROUP BY EXTRACT(YEAR FROM wo.reception_date), EXTRACT(MONTH FROM wo.reception_date)
ORDER BY year DESC, month DESC;
```

В листинге 40 представлен сложный запрос для вывода клиентов, потративших больше среднего, с процентным отклонением.

Листинг 40

```sql
WITH client_spending AS (
    SELECT 
        cl.id,
        cl.surname || ' ' || cl.first_name AS full_name,
        COUNT(DISTINCT wo.id) AS orders_count,
        SUM(wo.total_cost) AS total_spent
    FROM clients cl
    JOIN cars c ON cl.id = c.client_id
    JOIN work_orders wo ON c.id = wo.car_id
    WHERE wo.status = 'Выполнен'
    GROUP BY cl.id, cl.surname, cl.first_name
),
avg_spending AS (
    SELECT AVG(total_spent) AS avg_total_spent
    FROM client_spending
)
SELECT 
    cs.*,
    ROUND((cs.total_spent - a.avg_total_spent) / a.avg_total_spent * 100, 2) AS percent_above_avg
FROM client_spending cs
CROSS JOIN avg_spending a
WHERE cs.total_spent > a.avg_total_spent
ORDER BY cs.total_spent DESC;
```

В листинге 41 представлен сложный запрос для выбора наиболее часто используемых запчастей с рекомендацией по пополнению.

Листинг 41

```sql
SELECT 
    sp.part_name,
    sp.manufacturer,
    SUM(op.quantity) AS total_used,
    sp.quantity AS current_stock,
    CASE 
        WHEN sp.quantity = 0 THEN 'Нет в наличии'
        WHEN sp.quantity < 10 THEN 'Мало'
        ELSE 'Достаточно'
    END AS stock_status,
    CASE 
        WHEN sp.quantity < SUM(op.quantity) / 3.0 THEN 'Требуется пополнение'
        ELSE 'Запас достаточный'
    END AS reorder_recommendation
FROM spare_parts sp
JOIN order_parts op ON sp.id = op.part_id
GROUP BY sp.id, sp.part_name, sp.manufacturer, sp.quantity
ORDER BY total_used DESC;
```

В листинге 42 представлен сложный запрос для вывода заказов с просроченной датой выполнения.

Листинг 42

```sql
SELECT 
    wo.id AS order_id,
    c.brand || ' ' || c.model AS car,
    wo.reception_date,
    wo.status,
    CURRENT_DATE - wo.expected_completion_date AS days_overdue
FROM work_orders wo
JOIN cars c ON wo.car_id = c.id
WHERE wo.status NOT IN ('Выполнен', 'Отменен')
    AND CURRENT_DATE > wo.expected_completion_date
ORDER BY days_overdue DESC;