MySQL 8.0

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

create table weight_calc(cattle_id integer, weight integer, weight_date date);
insert into weight_calc values (10001,300,STR_TO_DATE("01-01-2022","%e-%m-%Y"));
insert into weight_calc values (10002,350,STR_TO_DATE("01-01-2022","%e-%m-%Y"));
insert into weight_calc values (10003,400,STR_TO_DATE("01-01-2022","%e-%m-%Y"));
insert into weight_calc values (10001,310,STR_TO_DATE("15-01-2022","%e-%m-%Y"));
insert into weight_calc values (10002,362,STR_TO_DATE("15-01-2022","%e-%m-%Y"));
insert into weight_calc values (10003,415,STR_TO_DATE("15-01-2022","%e-%m-%Y"));
insert into weight_calc values (10001,318,STR_TO_DATE("30-01-2022","%e-%m-%Y"));
insert into weight_calc values (10002,375,STR_TO_DATE("30-01-2022","%e-%m-%Y"));
insert into weight_calc values (10003,430,STR_TO_DATE("30-01-2022","%e-%m-%Y"));
select * from weight_calc


cattle_id
weight
weight_date


10001
300
2022-01-01

10002
350
2022-01-01

10003
400
2022-01-01

10001
310
2022-01-15

10002
362
2022-01-15

10003
415
2022-01-15

10001
318
2022-01-30

10002
375
2022-01-30

10003
430
2022-01-30


select 
cattle_id,weight,
row_number() over (partition by Cattle_ID order by weight_date asc) rn
from weight_calc


cattle_id
weight
rn


10001
300
1

10001
310
2

10001
318
3

10002
350
1

10002
362
2

10002
375
3

10003
400
1

10003
415
2

10003
430
3


with cte as(
select Cattle_ID,
case 
when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 3 then weight end last_weight,
case when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 2 then weight end 2ndlast_weight,
case when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 1 then weight end 3rdlast_weight,
weight_date
from weight_calc)
select cattle_id,max(last_weight) "Last weight", 
max(2ndlast_weight) "2nd Last Weight",
max(3rdlast_weight) "3rd Last Weight" ,
max(weight_date) "Last Weight Date"
from cte
group by cattle_id


cattle_id
Last weight
2nd Last Weight
3rd Last Weight
Last Weight Date


10001
318
310
300
2022-01-30

10002
375
362
350
2022-01-30

10003
430
415
400
2022-01-30


with cte_1 as(
select Cattle_ID,
case 
when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 3 then weight end last_weight,
case when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 2 then weight end 2ndlast_weight,
case when (row_number() over (partition by Cattle_ID order by weight_date asc)) = 1 then weight end 3rdlast_weight,
weight_date
from weight_calc
), cte_2 as (
select cattle_id,max(last_weight) lw, 
max(2ndlast_weight) lw_2,
max(3rdlast_weight) lw_3 ,
max(weight_date) lwd
from cte_1 
group by cattle_id)
select cte_2.cattle_id, 
cte_2.lw as "Last Weight", 
cte_2.lw_2 as "2nd Last Weight", 
cte_2.lw_3 as "3rd Last Weight", 
case when (lw - lw_3)>=30 then "Satisfactory" else "Not satisfactory" end remarks,
cte_2.lwd as "Last Weight"
from cte_2


cattle_id
Last Weight
2nd Last Weight
3rd Last Weight
remarks
Last Weight


10001
318
310
300
Not satisfactory
2022-01-30

10002
375
362
350
Not satisfactory
2022-01-30

10003
430
415
400
Satisfactory
2022-01-30

cattle_id	weight	weight_date
10001	300	2022-01-01
10002	350	2022-01-01
10003	400	2022-01-01
10001	310	2022-01-15
10002	362	2022-01-15
10003	415	2022-01-15
10001	318	2022-01-30
10002	375	2022-01-30
10003	430	2022-01-30

cattle_id	Last Weight	2nd Last Weight	3rd Last Weight	remarks	Last Weight
10001	318	310	300	Not satisfactory	2022-01-30
10002	375	362	350	Not satisfactory	2022-01-30
10003	430	415	400	Satisfactory	2022-01-30