Get the previous record based on conditions in Postgresql

Question

I have two tables, the 1st contains transaction details and the 2nd contains user's orders :

id | transaction_date
1  |   2019-01-01 
2  |   2019-02-01
3  |   2019-01-01


id | transaction_id | amount | user_id
15         1            7       1           
20         2            15      1
25         3            25      1

And I would like to have this result, that is to say for all users orders have also the previous amount he paid based on the transaction date.

user_id | amount | previous amount
   1         7           NULL
   1         15           7
   1         25           15

I tried multiple things including using the LAG function, but it doesn't seems to be possible with it because I have to join on another table to get the transaction_date. I think I should do a subquery with a left join but I don't figure out how to get only the previous order

Thanks

Answer 1

This is a join and lag():

select t2.user_id, t2.amount,
       lag(t2.amount) over (partition by t2.user_id order by t1.date) as prev_amount
from table1 t1 join
     table2 t2
     on t2.transaction_id = t1.id;