SELECT tax2.*,
                                                   imns_code AS recipient_imns_code,
                                                   short_name AS recipient_short_name,
                                                   date_registration AS recipient_date_registration
                                            FROM(
                                                SELECT tax1.*,
                                                        risk_group_code,
                                                        total_score,
                                                        DATE
                                                FROM (
                                                    SELECT  BR.recipient_unp,
                                                            BR.provider_unp,
                                                            full_exhibited_total_val,
                                                            full_signed_total_vat,
                                                            exhibited_total_val,
                                                            signed_total_vat,
                                                            signed_total_vat_rate,
                                                            kls_balance,
                                                            kls_balance_per_counterparty,
                                                            tax_gap_amount
                                                    FROM(

                                                        -- БП 171 --можно заменять argMax на any при работе с полями из документа для всех типов, кроме ADDITIONAL (для additional замена возможна только при фильтрации по УНП)
SELECT id,
       argMax(provider_unp, KEY)                          AS provider_unp,
       argMax(provider_is_resident, KEY)                  AS provider_is_resident,
       argMax(provider_key, KEY)                          AS provider_key,
       argMax(provider_name, KEY)                         AS provider_name,
       argMax(provider_declaration, KEY)                  AS provider_declaration,
       argMax(provider_taxes_number, KEY)                 AS provider_taxes_number,
       argMax(recipient_status_code, KEY)                 AS recipient_status_code,
       argMax(tuple(recipient_is_interdependence), KEY).1 AS recipient_is_interdependence,
       argMax(recipient_country_code, KEY)                AS recipient_country_code,
       argMax(recipient_unp, KEY)                         AS recipient_unp,
       argMax(recipient_is_resident, KEY)                 AS recipient_is_resident,
       argMax(recipient_key, KEY)                         AS recipient_key,
       argMax(recipient_name, KEY)                        AS recipient_name,
       argMax(recipient_declaration, KEY)                 AS recipient_declaration,
       argMax(recipient_taxes_number, KEY)                AS recipient_taxes_number,
       argMax(total_cost, KEY)                            AS total_cost,
       argMax(total_vat, KEY)                             AS total_vat,
       argMax(total_cost_vat, KEY)                        AS total_cost_vat,
       provider_recipient_key,
       argMax(pair_key, KEY)                              AS pair_key,
       date_from,
       argMax(external_date_load, KEY)                    AS external_date_load,
       MAX(KEY)                                           AS max_key,
       argMax(date_calculated, KEY)                       AS date_calculated
FROM mns_prod_dev_test.distributed_usf_invoices_actual
WHERE date_from_year = 2023
AND date_from_month <= 9
AND status_code <> 'ON_AGREEMENT'
AND (status_code <> 'CANCELLED' OR date_cancelled > 1672520399)
GROUP BY id, provider_status_code, provider_recipient_key, date_from
HAVING                                                                     -- опциональный фильтр
    provider_unp IN ('200034522') AND
   --добавить фильтры для получения БП 132
 ((provider_status_code IN ('SELLER', 'CONSIGNOR', 'TRUSTEE', 'TURNOVERS_ON_SALE_PAYER') AND
         recipient_unp = '112')
    OR (recipient_country_code NOT IN ('', '112')
            AND provider_declaration <> '' OR provider_taxes_number <> '')
            AND ((provider_status_code = 'SELLER' AND recipient_status_code = 'CUSTOMER')
            OR (provider_status_code = 'AGENT' AND recipient_status_code = 'CONSUMER')
            OR (provider_status_code = 'COMMISSIONAIRE' AND recipient_status_code = 'COMMISSIONAIRE')))
                                                    ) AS BR ALL LEFT JOIN (

                                                            SELECT payer_unp,
       argMax(full_exhibited_total_val, date_calculated)              AS full_exhibited_total_val,
       argMax(full_signed_total_vat, date_calculated)                 AS full_signed_total_vat,
       counterparty_unp,
       argMax(exhibited_total_val, date_calculated)                   AS exhibited_total_val,
       argMax(signed_total_vat, date_calculated)                      AS signed_total_vat,
       argMax(signed_total_vat_rate, date_calculated)                 AS signed_total_vat_rate,
       argMax(tuple(kls_balance), date_calculated).1                  AS kls_balance,
       argMax(tuple(kls_balance_per_counterparty), date_calculated).1 AS kls_balance_per_counterparty,
       argMax(tax_gap_amount, date_calculated)                        AS tax_gap_amount
FROM mns_prod_dev_test.distributed_tax_gaps
WHERE YEAR = 2023
  AND last_month = 9
              AND counterparty_unp IN ('200034522')
GROUP BY payer_unp, counterparty_unp
HAVING argMax(sign, date_calculated) = 1
                                                        ) AS tax ON BR.provider_unp = tax.payer_unp
                                                ) tax1   ALL LEFT JOIN  (

                                                                        -- GetRiskGroups
SELECT  unp,
                argMax(tuple(risk_group_code), date_calculated).1 AS risk_group_code,
                argMax(tuple(total_score), date_calculated).1 AS total_score,
                argMax(tuple(DATE), date_calculated).1 AS DATE
FROM (
        SELECT  unp,
                        risk_group_code,
                        total_score,
                        dictGetInt64('finished_risk_calculation_history_prod_dev_test', 'date', tuple(calculation_code)) AS DATE,
                        date_calculated
        FROM mns_prod_dev_test.distributed_risk_groups AS risk_group

                        prewhere YEAR * 12 + MONTH IN (24265, 24266, 24267, 24268, 24269, 24270, 24271, 24272, 24273, 24274, 24275, 24276)


                        -- UnpCodes filter
        AND toString(unp) IN ('200034522')
--


        WHERE dictGetUInt8('finished_risk_calculation_history_prod_dev_test', 'is_finished', tuple(calculation_code)) --обязательный фильтр


                AND DATE BETWEEN 1640984400 AND 1672520399


)
GROUP BY unp

--
                                                                ) AS risk_groups ON tax1.recipient_unp = risk_groups.unp
                                            ) tax2  ALL INNER JOIN (

                                                                -- GetGRPPayers with Typology1 filters
SELECT  unp,
        argMax(tuple(imns_code), date_load).1         AS imns_code,
        argMax(tuple(short_name), date_load).1        AS short_name,
        argMax(tuple(address), date_load).1           AS address,
        argMax(tuple(date_registration), date_load).1 AS date_registration,
        argMax(tuple(state_code), date_load).1        AS state_code,
        argMax(tuple(date_begin_state), date_load).1  AS date_begin_state,
        argMax(tuple(address_code), date_load).1      AS address_code,
        argMax(tuple(sign), date_load).1              AS sign
FROM mns_prod_dev_test.distributed_grp_actual_payers

    -- UnpCodes filter
        WHERE toString(unp) IN ('200034522')
--

GROUP BY unp
HAVING sign = 1 -- постоянный фильтр, без него будут грязные данные!!!
-- ImnsCodes filter
        AND imns_code IN ('000', '001', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '111', '112', '200', '201', '202', '203', '204', '205', '206', '207', '208', '209', '211', '213', '214', '215', '21
6', '217', '218', '232', '233', '241', '251', '271', '291', '292', '293', '294', '295', '300', '301', '302', '303', '304', '305', '306', '307', '308', '309', '311', '312', '313', '314', '315', '316', '317', '318', '319', '321
', '322', '323', '341', '351', '362', '363', '364', '371', '381', '391', '392', '393', '394', '395', '397', '399', '400', '401', '402', '403', '404', '405', '406', '407', '408', '409', '411', '412', '413', '414', '415', '416'
, '417', '418', '419', '421', '422', '423', '424', '432', '433', '434', '435', '491', '492', '493', '494', '495', '496', '497', '498', '499', '500', '501', '502', '503', '504', '505', '506', '507', '508', '509', '511', '512',
 '513', '514', '515', '516', '518', '519', '542', '543', '571', '591', '592', '593', '594', '595', '596', '600', '601', '602', '603', '604', '605', '606', '607', '608', '609', '611', '612', '613', '614', '615', '616', '617',
'618', '619', '621', '622', '623', '624', '625', '661', '671', '691', '692', '693', '694', '695', '696', '697', '700', '701', '702', '703', '704', '705', '707', '708', '709', '711', '712', '713', '715', '716', '717', '718', '719', '721', '722', '723', '724', '741', '751', '761', '772', '773', '774', '791', '792', '793', '794', '795', '796', '797', '800', '801', '000')
--

    -- RegistrationDate filters
--

--
                                                        ) AS RecipientGRP ON tax2.recipient_unp = RecipientGRP.unp;