Compare values ​​in duplicate tables

Question

I have a query that generates a table of duplicate records from a larger data set, and I would like to be able to perform further analysis on this output data.

Below is an example that can be used.

I would like to be able to identify, for each property, instances where the property's oldest rating was below 50 but the latest rating is now greater than 70.

In the example below, only ratings related to attribute 17 meet this condition, so the row containing Reference jygh will be extracted.

refer to	Attribute address	date	score
Adev	Attribute 12	2022-12-08	70
Pout	Attribute 12	2022-12-16	90
mhef	Properties 17	2023-01-02	25
jygh	Properties 17	2023-01-09	70
boy	Property 22	2022-10-05	85
qepl	Property 22	2022-10-25	28

This is a specialized analysis of change over time, so a max/min comparison of ratings alone is not sufficient.

Edit: I edited the data example to show the rating value decreasing, which does not meet the criteria.

Answer 1

This method only works if the date is not taken into account:

select PROPERTYADDRESS
from TABLE1
where RATING <= 50
and PROPERTYADDRESS in (
 select PROPERTYADDRESS
 from TABLE1
 where RATING >= 70
);

Check it out here: https://dbfiddle.uk/6yoRNP74

Answer 2

This will consider the oldest and newest rates:

SELECT mt.PROPERTYADDRESS    
FROM TABLE1 mt 
INNER JOIN
    (
        SELECT PROPERTYADDRESS, MIN(Date) AS MinDate
        FROM TABLE1
        GROUP BY PROPERTYADDRESS
    ) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MinDate
 WHERE RATING <= 50
 AND mt.PROPERTYADDRESS in (
     SELECT mt.PROPERTYADDRESS 
     FROM TABLE1 mt 
     INNER JOIN
        (
            SELECT PROPERTYADDRESS, MAX(Date) AS MaxDate
            FROM TABLE1
            GROUP BY PROPERTYADDRESS
        ) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MaxDate
     WHERE RATING >= 70
);

Check here: https://dbfiddle.uk/XzEIlvKc