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 ads as
(
Select *, ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Year) as rn
    from t
)
6 rows affected

CREATE TABLE first_rows AS
(
SELECT 
    a.item, 
    a.year, 
    a.price
FROM
    ads as a
WHERE rn=1
);

CREATE TABLE subs_rows AS
(
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 
    ads as a
LEFT JOIN
    ads as b
  ON a.item=b.item and a.rn=b.rn+1
WHERE a.rn>1
)
2 rows affected

4 rows affected

CREATE TABLE FINAL AS
(
SELECT item, price, year, NULL AS prev_year, NULL AS prev_price, NULL AS higher_than_previous_year FROM first_rows
UNION
SELECT item, price, year, prev_year, prev_price,higher_than_previous_year FROM subs_rows
)
6 rows affected

Select * from final
order by item, year


item
price
year
prev_year
prev_price
higher_than_previous_year


Hat
15
2020
null
null
null

Hat
17
2021
2020
15
yes

Hat
19
2022
2021
17
yes

Pen
3
2020
null
null
null

Pen
2
2021
2020
3
No

Pen
3.3
2022
2021
2
yes

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