Date in one table is before date in another table

I have a table 1

and Table 2

I need to get the following table where the date from table 1 is the closest (i.e. before) to the date from table 2 by id.

I assume I need to join two table where table1.id=table2.id and table1.date<=table2.date and then, rank to get the 'last' record in that merged table? Is it correct? Is there a simpler way?

CodePudding user response：

You can see structure and result in: dbfiddle

select
  distinct on (t1.id)
  t1.id,
  last_value(t1.type) over (order by to_date(t1.date, 'mm/dd/yyyy') desc)
from 
  table1 t1 inner join table2 t2 on t1.id = t2.id
where 
  to_date(t1.date, 'mm/dd/yyyy') <= to_date(t2.date, 'mm/dd/yyyy');