SELECT `bt`.`name`,
       `r`.`alias`        AS `rooms`,
       `d`.`deals_bank_id`,
       `ot`.`alias`       AS `object_type`,
       `d`.`reg_date`,
       `pbo`.`square`,
       `pdb`.`deals_buyer_type_id`,
       `d`.`agreement_date`,
       `con`.`concession` AS `concession`,
       `d`.`deals_concession_id`,
       `d`.`deals_seller_id`,
       `d`.`wholesale`,
       `hc`.`name`        AS `hc_name`,
       `loc`.`address`    AS `loc_address`,
       d.hash, d.deals_hash
FROM `parser_building_objects` `pbo`
         INNER JOIN `parser_deals_objects` `do`
                    ON do.building_object_id = pbo.id
         INNER JOIN
     (SELECT `d1`.`deals_object_id`,
             `d1`.`agreement_date`,
             `d1`.`reg_date`,
             `d1`.`document_date`,
             `d1`.`registration_number`,
             `d1`.`mortgage_term`,
             `d1`.`mortgage_number`,
             `d1`.`deals_concession_id`,
             `d1`.`deals_buyer_id`,
             `d1`.`deals_bank_id`,
             `d1`.`deals_seller_id`,
             `d1`.`wholesale`,
             `d1`.`price_square_r`,
             `d1`.`method_ppsmr_id`,
             `d1`.`compensation_amount`,
             `d1`.`ddu_amount`,
             d1.hash,
             d1.deals_hash
      FROM `parser_deals` `d1`
               INNER JOIN `parser_deals_objects` `do1`
                          ON do1.id = d1.deals_object_id
               LEFT JOIN
           (SELECT `do2`.`building_object_id`,
                   `d3`.*
            FROM `parser_deals` `d3`
                     INNER JOIN `parser_deals_objects` `do2`
                                ON do2.id = d3.deals_object_id
           ) `d2`
           ON (
                  do1.building_object_id = d2.building_object_id
                  )
               AND (d1.id <> d2.id)
               AND (
                  d1.agreement_date < d2.agreement_date
                  )
               AND (
                  d2.agreement_date <= '2019-10-21'
                  )
      WHERE d2.id IS NULL) `d`
     ON d.deals_object_id = do.id
         LEFT JOIN `parser_deals_concession` `con`
                   ON con.id = d.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 = d.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 = d.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 = d.deals_seller_id
         LEFT JOIN `parser_method_ppsmr` `ppsmr`
                   ON ppsmr.id = d.method_ppsmr_id
WHERE (
    `d`.`agreement_date` <= '2019-10-21'
    )
  AND (`b`.`housing_complex_id` = 75)
  AND (
    `d`.`agreement_date` <= '2019-08-21'
    )
  AND (
    `ot`.`alias` IN ('kvartira', 'apartamenty')
    )
GROUP BY `pbo`.`id`
HAVING NOT (
        (`d`.`deals_concession_id` = '2')
        AND (`d`.`deals_seller_id` = '2')
    )
ORDER BY `d`.`agreement_date`