Untitled

     #  #  #  A solution where the matching is performed on the side of the SQL server (need to compare performance):
    properties_current = select(Property.street_name,
                                Property.city,
                                Property.id,
                                Property.floor_area) \
        .where(and_(Property.quote_id == quote_id, Property.current == 1)).subquery()

    properties_past = select(Property.street_name,
                             Property.city,
                             Property.id,
                             Property.floor_area) \
        .where(and_(Property.quote_id == quote_id, Property.current == 0)).subquery()

    joined = properties_current.join(properties_past,
                                     # the below 2 lines light up in PyCharm linter as wrong type, but work as expected
                                     # (please don't change):
                                     (properties_current.c.street_name == properties_past.c.street_name)
                                     & (properties_current.c.city == properties_past.c.city),
                                     full=True)

    matched = select(joined).subquery()
    statement = select(matched.c.street_name.label("current_street_name"),
                       matched.c.city.label("current_city"),
                       matched.c.street_name_1.label("previous_street_name"),
                       matched.c.city_1.label("previous_city"),
                       matched.c.id.label("current_property_id"),
                       matched.c.id_1.label("previous_property_id"),
                       # matched.c.floor_area.label("current_floor_area"),  # uncomment for manual testing
                       # matched.c.floor_area_1.label("previous_floor_area"),  # uncomment for manual testing
                       func.abs(matched.c.floor_area - matched.c.floor_area_1).label("floor_area_diff")) \
        .order_by("floor_area_diff")

    pandas.read_sql_query(sql=statement, con=get_engine().connect())

    #  #  For manual testing of SELECT JOIN ON statement - preview SQL statement:
    # print(statement)
    #  #  For manual testing of SELECT JOIN ON statement - view raw results of executing the SELECT JOIN ON statement:
    # print(matched_properties_dataset.to_string())