I have the two following tables:

CREATE TABLE my_table1 (
    color VARCHAR(50),
    d1 DATE,
    var1 VARCHAR(50)
);

INSERT INTO my_table1 (color, d1, var1)
VALUES
    ('red', '2010-01-01', 'a'),
    ('red', '2011-01-01', 'b'),
    ('red', '2012-01-01', 'c'),
    ('red', '2013-01-01', 'b'),
    ('blue', '2013-01-01', 'a'),
    ('blue', '2014-01-01', 'd'),
    ('blue', '2015-01-01', 'f'),
    ('green', '2020-01-01', 'a');


CREATE TABLE my_table2 (
    color VARCHAR(50),
    d2 DATE,
    var2 VARCHAR(50)
);


INSERT INTO my_table2 (color, d2, var2)
VALUES
    ('red', '2008-01-01', 'zz'),
    ('red', '2020-01-01', 'cc'),
    ('blue', '1999-01-01', 'xx'),
    ('blue', '2005-01-01', 'yy'),
    ('purple', '2022-01-01', 'hh');

These tables look like this:

| color |     d1     | var1 |
|-------|------------|------|
|  red  | 2010-01-01 |   a  |
|  red  | 2011-01-01 |   b  |
|  red  | 2012-01-01 |   c  |
|  red  | 2013-01-01 |   b  |
| blue  | 2013-01-01 |   a  |
| blue  | 2014-01-01 |   d  |
| blue  | 2015-01-01 |   f  |
| green | 2020-01-01 |   a  |


| color  |     d2     | var2 |
|--------|------------|------|
|  red   | 2008-01-01 |  zz  |
|  red   | 2020-01-01 |  cc  |
|  blue  | 1999-01-01 |  xx  |
|  blue  | 2005-01-01 |  yy  |
| purple | 2022-01-01 |  hh  |

I am trying to accomplish the following task:

• in my_table1, identify the most recent row for each color (based on the year column, i.e. d1)
• then, join this to my_table2
• the final result should have the same number of rows as my_table2 (i.e. if a color in my_table2 does not exist in my_table1, then NULL)

To do this, I thought I could first create a CTE where I identify the most recent row for each color in my_table1. Then, I perform a left outer join to ensure that all rows from my_table2 are kept.

Here is my attempt to do this:

WITH most_recent AS (
    SELECT 
        color, 
        d1 AS most_recent_d1, 
        var1 AS most_recent_var1,
        ROW_NUMBER() OVER(PARTITION BY color ORDER BY d1 DESC) as rn
    FROM 
        my_table1
)
SELECT 
    t2.color, 
    t2.d2, 
    t2.var2, 
    mr.color AS most_recent_color, 
    mr.most_recent_d1, 
    mr.most_recent_var1
FROM 
    my_table2 t2
LEFT OUTER JOIN 
    most_recent mr
ON 
    t2.color = mr.color
AND 
    mr.rn = 1;

Is this the correct logic?

Thanks!