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 LEFT 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
    -- RegistrationDate filters
--

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