SQL, left join table, How to keep only one value?

Question

I would like to join two tables. The table 1 is like this:

id      Date
a     01.01.2021
a     02.01.2021
a     03.01.2021
b     01.01.2021
b     02.01.2021
b     03.01.2021
c     01.01.2021
c     02.01.2021
c     03.01.2021

The table 2 is like this:

id  value 
a    12
a     8
b    50

As final result, I would like to have a table like this: for an existing id from table 2, the sum of the value of the same id should be stored in the last date in the table 1.

   id       Date     Value_FINAL
   a     01.01.2021    0
   a     02.01.2021    0
   a     03.01.2021    20
   b     01.01.2021    0
   b     02.01.2021    0
   b     03.01.2021    50
   c     01.01.2021    0
   c     02.01.2021    0
   c     03.01.2021    0

I tried to use left join to join these two tables at first,

with t3 as ( select id, sum(value) Value_FINAL from t2 group by id) 
            select t1.*, t3.value_FINAL from t1 left join t3 on t1.id = t3.id;

After this, I can get this:

   id       Date     Value_FINAL
   a     01.01.2021    20
   a     02.01.2021    20
   a     03.01.2021    20
   b     01.01.2021    50
   b     02.01.2021    50
   b     03.01.2021    50
   c     01.01.2021    0
   c     02.01.2021    0
   c     03.01.2021    0

But, it is not I want. can someone help with this? How can I keep the value only in the last Date in the column 'value_FINAL' I am also thinking about to use last_value(value) over (partition by id order by date). But I need to create an extra table or column.
maybe someone has a good idea how to deal this problem?

Answer 1

A join based alternative

 select a.*, coalesce(c.value,0)
 from t1 a
 left join (select id, max(date) date from t1 group by id) b on a.id = b.id and a.date = b.date
 left join (select id, sum(value) value  from t2 group by id) c on b.id = c.id