SQL - Including groups with most recent ownership

Question

My dataset includes tests on owned devices, but a device can be bought and resold, and we only want the tests associated with the most recent ownership.

The data set looks like this:

TestID               ProductID       RegistrationID     TestDate
00021357916020      P3K25EL141297        89D617         2019-07-22
00021357915928      P3K25EL141297        89D617         2019-07-15
00021353422334      P3K25EL141297        5PKY90         2019-05-31

I need to refine to only include the tests for Registration 89D617, as those are the tests with the most recent ownership. We're not provided with any reference information for the ownership change, and the registration IDs do not increase in a meaningful order so I've haven't been able to figure a means of grouping these.

To clarify - the table would need to include both entries with RegistrationID of 89D617 as those are all tests with the most recent registration.

Answer 1

With FIRST_VALUE() window function:

SELECT t.TestID, t.ProductID, t.RegistrationID, t.TestDate
FROM (
  SELECT *, FIRST_VALUE(RegistrationID) OVER (PARTITION BY ProductID ORDER BY TestDate DESC) reg
  FROM tablename  
) t
WHERE t.RegistrationID = t.reg

See the demo.
Results:

> TestID      | ProductID     | RegistrationID | TestDate               
> :---------- | :------------ | :------------- | :---------
> 21357916020 | P3K25EL141297 | 89D617         | 2019-07-22
> 21357915928 | P3K25EL141297 | 89D617         | 2019-07-15