Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 9.6.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


Create table t (Item varchar(10), Year INT ,Price float);
Insert into t values
('Hat', 2020, 15),
('Hat', 2021, 17),
('Hat', 2022, 19),
('Pen', 2020, 3),
('Pen', 2021, 2),
('Pen', 2022, 3.3);
6 rows affected

select * from t


item
year
price


Hat
2020
15

Hat
2021
17

Hat
2022
19

Pen
2020
3

Pen
2021
2

Pen
2022
3.3


CREATE TABLE FINAL AS
(
SELECT 
    a.item, 
    a.year, 
    a.price, 
    NULL AS prev_year, 
    NULL AS prev_price,
    NULL AS higher_than_previous_year
FROM
    (
    Select *, ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Year) as rn
    from t
    ) as a
WHERE rn=1

UNION

SELECT 
    a.item, 
    a.year, 
    a.price, 
    b.year as prev_year, 
    b.price as prev_price,
  CASE
    WHEN a.price > b.price THEN 'yes'
    ELSE 'No'
  END AS higher_than_previous_year
FROM 
    (
    Select *, ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Year) as rn
    from t
    ) as a
LEFT JOIN
    (
    Select *, ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Year) as rn
    from t
    ) as b
  ON a.item=b.item and a.rn=b.rn+1
WHERE a.rn>1
)
6 rows affected

Select * from final
order by item, year


item
year
price
prev_year
prev_price
higher_than_previous_year


Hat
2020
15
null
null
null

Hat
2021
17
2020
15
yes

Hat
2022
19
2021
17
yes

Pen
2020
3
null
null
null

Pen
2021
2
2020
3
No

Pen
2022
3.3
2021
2
yes

item	year	price	prev_year	prev_price	higher_than_previous_year
Hat	2020	15	null	null	null
Hat	2021	17	2020	15	yes
Hat	2022	19	2021	17	yes
Pen	2020	3	null	null	null
Pen	2021	2	2020	3	No
Pen	2022	3.3	2021	2	yes