SELECT d1.id,
       `bt`.`name`,
       `r`.`alias`        AS `rooms`,
       `d1`.`deals_bank_id`,
       `ot`.`alias`       AS `object_type`,
       `d1`.`reg_date`,
       `pbo`.`square`,
       `pdb`.`deals_buyer_type_id`,
       `d1`.`agreement_date`,
       `con`.`concession` AS `concession`,
       `d1`.`deals_concession_id`,
       `d1`.`deals_seller_id`,
       `d1`.`wholesale`,
       `hc`.`name`        AS `hc_name`,
       `loc`.`address`    AS `loc_address`
FROM (
    -- основной запрос по сделкам
         SELECT d.id,
                `d`.`deals_object_id`,
                `d`.`agreement_date`,
                `d`.`reg_date`,
                `d`.`document_date`,
                `d`.`registration_number`,
                `d`.`mortgage_term`,
                `d`.`mortgage_number`,
                `d`.`deals_concession_id`,
                `d`.`deals_buyer_id`,
                `d`.`deals_bank_id`,
                `d`.`deals_seller_id`,
                `d`.`wholesale`,
                `d`.`price_square_r`,
                `d`.`method_ppsmr_id`,
                `d`.`compensation_amount`,
                `d`.`ddu_amount`,
                pbo.id as pbo_id
         from parser_deals d
                  inner join parser_deals_objects pdo on d.deals_object_id = pdo.id
                  inner join parser_building_objects pbo on pdo.building_object_id = pbo.id
                  inner join parser_building pb on pbo.building_id = pb.id
         -- фильтрация по дате и проекту
         where pb.housing_complex_id = 75
           and d.agreement_date <= '2019-10-21'
         -- группировка по ПД и дате
         GROUP BY pbo.id, d.agreement_date
     ) `d1`

    -- JOIN который фильтрует первый запрос
    -- Берём тут максимальную дату сделок, группированых по ПД и отфильтрованных по проекту и дате
         INNER JOIN
     (
         -- MAX(agreement_date)
         SELECT d.id, MAX(d.agreement_date) as agr_date, d.deals_object_id, pbo.id as pbo_id
         from parser_deals d
                  inner join parser_deals_objects pdo on d.deals_object_id = pdo.id
                  inner join parser_building_objects pbo on pdo.building_object_id = pbo.id
                  inner join parser_building pb on pbo.building_id = pb.id
         where pb.housing_complex_id = 75
           and d.agreement_date <= '2019-10-21'
         GROUP BY pbo.id
     ) `d2`
         -- Сама фильтрация
         ON d1.agreement_date = d2.agr_date AND d1.pbo_id = d2.pbo_id

        -- JOINы для вывода данных
         LEFT JOIN parser_deals_objects pdo
                   ON d1.deals_object_id = pdo.id
         LEFT JOIN parser_building_objects pbo
                   on pdo.building_object_id = pbo.id
         LEFT JOIN `parser_deals_concession` `con`
                   ON con.id = d1.deals_concession_id
         LEFT JOIN `parser_object_type` `ot`
                   ON ot.id = pbo.object_type_id
         LEFT JOIN `parser_deals_buyers` `pdb`
                   ON pdb.id = d1.deals_buyer_id
         LEFT JOIN `parser_deals_buyer_type` `bt`
                   ON bt.id = pdb.deals_buyer_type_id
         LEFT JOIN `parser_rooms` `r`
                   ON r.id = pbo.custom_rooms_id
         LEFT JOIN `parser_deals_banks` `bank`
                   ON bank.id = d1.deals_bank_id
         LEFT JOIN `parser_building` `b`
                   ON b.id = pbo.building_id
         LEFT JOIN `parser_housing_complex` `hc`
                   ON hc.id = b.housing_complex_id
         LEFT JOIN `core_location` `loc`
                   ON loc.id = b.location_id
         LEFT JOIN `parser_region` `region`
                   ON region.id = b.region_id
         LEFT JOIN `parser_objectclass` `class`
                   ON class.id = b.objectclass_id
         LEFT JOIN `parser_developer` `dev`
                   ON dev.id = b.developer_id
         LEFT JOIN `parser_builder` `br`
                   ON br.id = b.builders_id
         LEFT JOIN `parser_deals_seller` `ds`
                   ON ds.id = d1.deals_seller_id
         LEFT JOIN `parser_method_ppsmr` `ppsmr`
                   ON ppsmr.id = d1.method_ppsmr_id
-- ещё фильтры
where `ot`.`alias` IN ('kvartira', 'apartamenty')
AND NOT (`d1`.`deals_concession_id` = '2' AND `d1`.`deals_seller_id` = '2')