Create a repost of latest and second latest value

Question

I have products audits table looks like this

id	product_id	column_updated	value	timestamp
1	product_1	name	Big Shoes.	"18 September 2022 6:42:50 PM GMT+05:30"
2.	product_1	name	Green Shoes	"18 September 2022 6:42:43 PM GMT+05:30"
3.	product_1	name	Big Green Shoes	"18 September 2022 6:43:43 PM GMT+05:30"

I want to show report of latest change happened on column in form like below

product_id	column_updated	latest_value	previous_value
product_1	name	Green Shoes	Big Green Shoes

I have prepared a query to fetch last 2 record but not sure how I can merge them to form a view like this?

my query is

select product_id, column_updated, value 
from audits 
where product_id = 'product_1' 
 and column_updated = 'name' 
order by timestamp desc 
limit 2;

Please suggest any approach for this, Thanks in advance!

Answer 1

You need to use LEADa swindow function to hget the latest and previous value

WITH CTE as
(select product_id,
   column_updated,
   value as latest_value,
     lead(value) over (
         partition by product_id,column_updated order by timestamp desc
         ) as previous_value,
       ROW_NUMBER() over (
         partition by product_id,column_updated order by timestamp desc
         ) rn
     
    from audits 
where product_id = 'product_1' 
 and column_updated = 'name')
SELECT product_id, column_updated,latest_value,previous_value FROM CTE WHERE rn = 1

product_id	column_updated	latest_value	previous_value
product_1	name	Big Green Shoes	Big Shoes.

SELECT 1

fiddle