By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table mytable (
arrival_date date,
departure_date date,
guest_number int
);
insert into mytable values
('2022-01-01', '2022-01-02', 2),
('2022-01-02', '2022-01-03', 3);
Records: 2 Duplicates: 0 Warnings: 0
select `date`, sum(guest_number) as total_guest_number
from (
select arrival_date as `date`, guest_number
from mytable
union all
select departure_date as `date`, guest_number
from mytable
) as s
group by `date`
| date | total_guest_number |
|---|---|
| 2022-01-01 | 2 |
| 2022-01-02 | 5 |
| 2022-01-03 | 3 |