MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table bird (
id int,
name varchar(15),
sex enum('male','female') );

insert into bird values  (1,'bob','male'), (2,'mary','female'), (3,'jono','male');

create table `check` (
id int,
check_date date  ,
bird_id int );
insert into `check` values  (1,'2023-06-01',2), (2,'2023-07-01',2),(3,'2023-08-01',2);

create table weights (
id int,
weight decimal(5,2)  ,
check_id int );

insert into weights values (1,3.3,2),(2,3.45,2),(3,3.8,2),(4,4.5,1),(5,4.51,1);

create table measurements (
id int,
measur decimal(5,2)  ,
check_id int );

insert into measurements values (1,95.2,1),(2,97.4,1),(3,96.4,1),(4,99.1,3),(5,98.1,3);





Records: 3  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0


select * from bird;
select * from `check`;
select * from weights;
select * from measurements;


id
name
sex


1
bob
male

2
mary
female

3
jono
male




id
check_date
bird_id


1
2023-06-01
2

2
2023-07-01
2

3
2023-08-01
2




id
weight
check_id


1
3.30
2

2
3.45
2

3
3.80
2

4
4.50
1

5
4.51
1




id
measur
check_id


1
95.20
1

2
97.40
1

3
96.40
1

4
99.10
3

5
98.10
3


select b.id as bird_id,
       b.name as bird_name,
       max(c.check_date) as newest_check
from bird b 
inner join `check` c on b.id=c.bird_id
group by bird_id,bird_name;


bird_id
bird_name
newest_check


2
mary
2023-08-01


select  MAX(c.check_date) as date_newest_measurements,
        ROUND(AVG(m.measur),2) as avg_newest_measurements,
        c.bird_id
from  `check` c  
inner join measurements m on c.id =(select max(m.check_id) from measurements m where exists (select id from `check` c where c.bird_id = 2))
where c.bird_id = 2 ;


date_newest_measurements
avg_newest_measurements
bird_id


2023-08-01
97.24
2


select MAX(c.check_date) as date_newest_weight,
       ROUND(AVG(w.weight),2) as avg_newest_weight,
       c.bird_id
from  `check` c  
inner join weights w on c.id = (select max(w.check_id) from weights w where exists (select id from `check` c where c.bird_id = 2))
where c.bird_id = 2 
group by c.bird_id;


date_newest_weight
avg_newest_weight
bird_id


2023-07-01
3.91
2


select b.id as bird_id,
       b.name as bird_name,
       max(c.check_date) as newest_check,
       date_newest_measurements,
       avg_newest_measurements,
       date_newest_weight,
       avg_newest_weight
from bird b 
inner join `check` c on b.id=c.bird_id
inner join ( select  MAX(c.check_date) as date_newest_measurements,
                     ROUND(AVG(m.measur),2) as avg_newest_measurements,
                     c.bird_id
             from  `check` c  
             inner join measurements m on c.id =(select max(m.check_id) from measurements m where exists (select id from `check` c where c.bird_id = 2)) 
             where c.bird_id = 2 
             group by c.bird_id
           ) as max_meas  on max_meas.bird_id=c.bird_id
inner join (
            select MAX(c.check_date) as date_newest_weight,
                   ROUND(AVG(w.weight),2) as avg_newest_weight,
                   c.bird_id
            from  `check` c  
            inner join weights w on c.id = (select max(w.check_id) from weights w where exists (select id from `check` c where c.bird_id = 2))
            where c.bird_id = 2 
            group by c.bird_id
           )  as max_weig on max_weig.bird_id=c.bird_id
group by bird_id,
         bird_name,
         date_newest_measurements,
         avg_newest_measurements,
         date_newest_weight,
         avg_newest_weight;


bird_id
bird_name
newest_check
date_newest_measurements
avg_newest_measurements
date_newest_weight
avg_newest_weight


2
mary
2023-08-01
2023-08-01
97.24
2023-07-01
3.91