SQL Code

WITH max_dates AS (
    SELECT 
        region_id, 
        MAX(date) AS max_date
    FROM default.price_parsing
    GROUP BY region_id
),

pars AS (
    SELECT 
    pp.model_id, 
    GREATEST(pp.price, pp.price2) / 1.2 AS least, 
    pp.source, 
    pp.url, 
    pp.region_id,
    ROW_NUMBER() OVER (PARTITION BY pp.model_id, pp.region_id ORDER BY LEAST(pp.price, pp.price2) / 1.2, pp.source, pp.region_id) AS rn
FROM default.price_parsing pp
JOIN max_dates md 
    ON pp.region_id = md.region_id 
    AND pp.date = md.max_date
),

a AS (
    SELECT *
    FROM pars
    WHERE rn = 1
),

b AS (
    SELECT 
        so.create_ts sub_creates, o.create_ts order_create, oi.model_id, oi.mtr_code, o.client_id, o.be_id be_id, csb.region_id, so.supplier_id supplier_id, so.order_number so_order_number, o.order_number order_number,
        CASE 
            WHEN csb.region_id = '1490490e-49c5-421c-9572-5673ba5d80c8' THEN 'Липецкая обл.'
            WHEN csb.region_id = 'd028ec4f-f6da-4843-ada6-b68b3e0efa3d' THEN 'Тульская обл.'
            WHEN csb.region_id = 'b6ba5716-eb48-401b-8443-b197c9578734' THEN 'Забайкальский край'
            WHEN csb.region_id = 'da051ec8-da2e-4a66-b542-473b8d221ab4' THEN 'Волгоградская обл.'
            WHEN csb.region_id = '4f8b1a21-e4bb-422f-9087-d3cbf4bebc14' THEN 'Пермский край'
            WHEN csb.region_id = 'c2deb16a-0330-4f05-821f-1d09c93331e6' THEN 'г. Санкт-Петербург'
            WHEN csb.region_id = '7d468b39-1afa-41ec-8c4f-97a8603cb3d4' THEN 'Хабаровский край'
            WHEN csb.region_id = 'db9c4f8b-b706-40e2-b2b4-d31b98dcd3d1' THEN 'Красноярский край'
            WHEN csb.region_id = '327a060b-878c-4fb4-8dc4-d5595871a3d8' THEN 'Ставропольский край'
            WHEN csb.region_id = '9c05e812-8679-4710-b8cb-5e8bd43cdf48' THEN 'Магаданская обл.'
            WHEN csb.region_id = '43909681-d6e1-432d-b61f-ddac393cb5da' THEN 'Приморский край'
            WHEN csb.region_id = '8d3f1d35-f0f4-41b5-b5b7-e7cadf3e7bd7' THEN 'Респ. Хакасия'
            WHEN csb.region_id = '6466c988-7ce3-45e5-8b97-90ae16cb1249' THEN 'Иркутская обл.'
            WHEN csb.region_id = '393aeccb-89ef-4a7e-ae42-08d5cebc2e30' THEN 'Кемеровская область - Кузбасс'
            WHEN csb.region_id = 'c225d3db-1db6-4063-ace0-b3fe9ea3805f' THEN 'Респ. Саха /Якутия/'
            WHEN csb.region_id = 'd00e1013-16bd-4c09-b3d5-3cb09fc54bd8' THEN 'Краснодарский край'
            WHEN csb.region_id = '1c727518-c96a-4f34-9ae6-fd510da3be03' THEN 'Мурманская обл.'
            WHEN csb.region_id = '0c5b2444-70a0-4932-980c-b4dc0d3f02b5' THEN 'г. Москва'
            WHEN csb.region_id = '6d1ebb35-70c6-4129-bd55-da3969658f5d' THEN 'Ленинградская обл.'
            WHEN csb.region_id = '29251dcf-00a1-4e34-98d4-5c47484a36d4' THEN 'Московская обл.'
            WHEN csb.region_id = '92b30014-4d52-4e2e-892d-928142b924bf' THEN 'Свердловская обл.'
            WHEN csb.region_id = '27eb7c10-a234-44da-a59c-8b1f864966de' THEN 'Челябинская обл.'
            WHEN csb.region_id = '54049357-326d-4b8f-b224-3c6dc25d6dd3' THEN 'Тюменская обл.'
            WHEN csb.region_id = '0c089b04-099e-4e0e-955a-6bf1ce525f1a' THEN 'Респ.Татарстан'
            ELSE 'Неизвестный регион'
        END AS region_name,
        AVG(oi.unit_delivery_price) as unit_delivery, 
        SUM(oi.quantity) as counts, AVG(oi.unit_price) as unit_price, 
        SUM(oi.quantity * (oi.unit_price + oi.unit_delivery_price)) as GMV_delivery,
        SUM(oi.quantity * oi.unit_price) as GMV
    FROM order_service.order_item oi
    LEFT JOIN order_service.supplier_order so ON oi.supplier_order_id = so.id
    LEFT JOIN order_service.order o ON oi.order_id = o.id
    LEFT JOIN bi.source__client_service_basis csb ON o.basis_id = CAST(csb.id AS Nullable(Int64))
    WHERE so.system_status NOT IN ('CANCELED', 'DECLINED', 'DRAFT')
    GROUP BY oi.model_id, oi.mtr_code, o.client_id, so.create_ts, o.create_ts, o.be_id, csb.region_id, so.supplier_id, so.order_number, o.order_number
)

SELECT DISTINCT
    b.model_id, 
    dictGet('pim_catalog_model_dict', 'name', b.model_id) as model_name,
    b.supplier_id,
    dictGet('supplier_service_supplier_dict', 'name', b.supplier_id) as supplier_name,
    b.order_number,
    b.so_order_number,
    dictGet('dictionary_service_region_dict', 'name_with_type', b.region_id) AS region_name,
    dictGet('pim_catalog_category_dict', 'name', ps.category_id) as category_name,
    dictGet('pim_catalog_category_dict', 'top_name', ps.category_id) as top_category_name,
    arrayElement(splitByString(',', dictGet('pim_catalog_category_dict', 'category_path', ps.category_id)), 3) AS third_category,
    b.mtr_code as "Код ОЗМ", 
    client_category,
    b.client_id, 
    dictGet('client_service_business_entities_dict', 'legal_entity', b.be_id) be,
    b.sub_creates,
    b.order_create,
    b.unit_delivery as "Цена доставки единицы", 
    b.counts as "Количество заказано", 
    b.unit_price as "Цена единицы в факте без НДС", 
    b.GMV as "GMV МП",
    b.GMV_delivery,
    a.least as "МинЦена парсинга без НДС", 
    a.least * b.counts as "GMV Парсинга",
    a.source as "Сайт минимальной цены парсинга", 
    (b.unit_price / a.least - 1) * 100 as "Сравнение, %",
    
    CASE 
        WHEN ((b.unit_price / a.least - 1) * 100) > 80 THEN 'Выброс, МП дороже'
        WHEN ((b.unit_price / a.least - 1) * 100) <= 80 AND ((b.unit_price / a.least - 1) * 100) > 0 THEN 'МП дороже'
        WHEN ((b.unit_price / a.least - 1) * 100) < -60 THEN 'Выброс, МП дешевле'
        WHEN ((b.unit_price / a.least - 1) * 100) >= -60 AND ((b.unit_price / a.least - 1) * 100) <= 0 THEN 'МП дешевле'
    END AS Comment,
    
    CASE 
        WHEN ((b.unit_price / a.least - 1) * 100) > 80 THEN '1'
        WHEN ((b.unit_price / a.least - 1) * 100) <= 80 AND ((b.unit_price / a.least - 1) * 100) > 0 THEN '2'
        WHEN ((b.unit_price / a.least - 1) * 100) < -60 THEN '4'
        WHEN ((b.unit_price / a.least - 1) * 100) >= -60 AND ((b.unit_price / a.least - 1) * 100) <= 0 THEN '3'
    END AS orderby,
    
    CASE 
        WHEN ((b.unit_price / a.least - 1) * 100) > 80
        OR ((b.unit_price / a.least - 1) * 100) < -60 THEN 'Выброс'
        WHEN ((b.unit_price / a.least - 1) * 100) >= -60 AND ((b.unit_price / a.least - 1) * 100) <= 80 THEN 'Не выброс'
    END AS "Выброс",
    
    CASE
        WHEN dictGet('client_service_business_entities_dict', 'legal_entity', b.be_id) IN (
            'АО "БМЗ"',
            'АО "Ковдорский ГОК"',
            'АО "НАК "Азот"',
            'АО "Невинномысский Азот"',
            'ООО "ЕвроХим-БМУ"',
            'ООО "ЕвроХим - УКК"',
            'ООО "ЕвроХим-ВолгаКалий"',
            'ООО "НевРСС"',
            'ООО "Новомосковский хлор"'
        ) THEN 'ЕХ'
    
        WHEN dictGet('client_service_business_entities_dict', 'legal_entity', b.be_id) IN (
            'ООО "СИБ - ДАМЕЛЬ"',
            'АО "СУЭК-Красноярск"',
            'АО "СУЭК-Кузбасс"',
            'АО "УПиР"',
            'ООО "ЕСК-СУЭК"'
        ) THEN 'СУЭК'
    
        -- Добавляем новые юрлица из второго запроса
        WHEN dictGet('client_service_business_entities_dict', 'legal_entity', b.be_id) IN (
            'АО "Лучегорский угольный разрез"',
            'АО "Разрез Тугнуйский"',
            'АО "Разрез Харанорский"',
            'АО "Ургалуголь"',
            'ООО "Восточно-Бейский разрез"',
            'ООО "Приморскуголь"',
            'ООО "СУЭК-Хакасия"',
            'ООО "Тугнуйская ОФ"',
            'АО "СибЭР"'
        ) THEN 'СУЭК'
    
        WHEN dictGet('client_service_business_entities_dict', 'legal_entity', b.be_id) IN (
            'АО "ЕвроХим-Северо-Запад"',
            'ООО "Кингисепп-ремстройсервис"',
            'ООО "НовомосковскРСС"',
            'ООО "ПГ "Фосфорит"',
            'ООО "ПроТехСтрой"',
            'Филиал АО "НАК "Азот" Новомосковская ГРЭС"'
        ) THEN 'ЕХ'
    
        ELSE NULL
    END AS EXorCYEK,
    
    a.url as "Ссылка сайта"
FROM b
LEFT JOIN a ON b.model_id = a.model_id AND toString(b.region_id) = a.region_id
LEFT JOIN (SELECT model_id, category_id FROM default.price_snapshot WHERE date = today() - 7) as ps ON b.model_id = ps.model_id

-- Коннект категорий клиента
LEFT JOIN (
    select ir.id mtr_code, cr.name client_category, cr.code
    from bi.source__product_mapping_api_item_reference ir
    left join bi.source__product_mapping_api_category_reference cr on ir.category_id = cr.id
) as connect on connect.mtr_code = b.mtr_code
WHERE a.least > 0
and dictGet('pim_catalog_category_dict', 'name', ps.category_id) NOT IN ('Без категории')
[[AND client_id = {{client_id}}]]
[[AND dictGet('pim_catalog_category_dict', 'name', a.category_id) = {{category_name}}]]
[[AND date(b.sub_creates) >= {{date}}]]
[[and LOWER(dictGet('client_service_business_entities_dict', 'legal_entity', b.be_id))  LIKE CONCAT('%',LOWER({{be}}),'%') ]] 
[[and LOWER(dictGet('dictionary_service_region_dict', 'name_with_type', b.region_id))  LIKE CONCAT('%',LOWER({{region_name}}),'%') ]]
[[AND 
    CASE 
        WHEN ((b.unit_price / a.least - 1) * 100) > 80 OR ((b.unit_price / a.least - 1) * 100) < -60 THEN 'Выброс'
        WHEN ((b.unit_price / a.least - 1) * 100) >= -60 AND ((b.unit_price / a.least - 1) * 100) <= 80 THEN 'Не выброс'
    END = {{vibros}}]]
ORDER BY b.model_id