query

WITH tranches_count AS (SELECT arc.nip, max(art.number) AS tranches_count
                        FROM axpo_rozliczenia_contract arc
                                 JOIN axpo_rozliczenia_pointofdelivery a ON arc.id = a.contract_id
                                 JOIN axpo_rozliczenia_tranche art ON a.id = art.ppe_id
                        WHERE 1=1
                        AND date_part('year', settlement_date) = 2022
                        AND date_part('month', settlement_date) = 4
                        GROUP BY arc.nip),
     min_years AS (
         SELECT arc2.contract_number,
                a.number,
                min(date_part('year', arm.date)) AS min_year
         FROM axpo_rozliczenia_contract arc2
                  JOIN axpo_rozliczenia_pointofdelivery a ON arc2.id = a.contract_id
                  JOIN axpo_rozliczenia_monthlyvolume arm ON a.id = arm.point_of_delivery_id
         GROUP BY arc2.contract_number, a.number
     )
SELECT DISTINCT c.bzs_date                                           AS "Data Weryfikacji Pozytywnej BZS",
                c.settlement_date                                       AS "Data Rozliczenia",
                c.partner_number                                     AS "Numer Partnera",
                c.client_name                                        AS "Nazwa Klienta",
                c.nip                                                AS "NIP",
                c.contract_number                                    AS "Numer Umowy",
                c.employee_first_name || ' ' || c.employee_last_name AS Pracownik,
                c.employee_number                                    AS "Id Doradcy Klienta",
                arp.name                                             AS "Nazwa PPE",
                arp.number                                           AS "Numer PPE/PPG",
                c.product_name                                       AS "Nazwa produktu",
                c.contract_id AS "contract_id",
                arp.tariff                                           AS "Taryfa",
                CASE WHEN c.partner_number='P/155' then '-'
                else arm.zone
                END
                AS "Strefa",
                c.product_edition,
                coalesce(l.tranches_count, 0)                        AS tranches_count,
                cast(my.min_year AS integer)                         AS min_year
FROM axpo_rozliczenia_contract c
         JOIN axpo_rozliczenia_pointofdelivery arp ON c.id = arp.contract_id
         JOIN axpo_rozliczenia_monthlyvolume arm ON arp.id = arm.point_of_delivery_id
         LEFT JOIN tranches_count l ON c.nip = l.nip
         JOIN min_years my ON c.contract_number = my.contract_number AND arp.number = my.number
WHERE medium_id = 2
AND partner_number LIKE '%%'
AND date_part('year', settlement_date) = 2022
AND date_part('month', settlement_date) = 4
ORDER BY c.contract_number, arp.number, "Strefa";