Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

create table apples_consumption (
  report_date date, apples_consumed int
);

insert into apples_consumption values ('2022-01-01', 5), ('2022-02-01', 7), ('2022-03-01', 2);

create table hotel_visitors (
  visitor_id int, check_in_date date, check_out_date date
);

insert into hotel_visitors values (1, '2021-12-01', '2022-02-01'), (2, '2022-01-01', null), (3, '2022-02-01', null), (4, '2022-03-01', null);

CREATE TABLE

INSERT 0 3

CREATE TABLE

INSERT 0 4

select a.report_date, count(v.visitor_id) as visitors_count, a.apples_consumed
  from apples_consumption a
       left join hotel_visitors v
         on a.report_date 
              between v.check_in_date 
                and coalesce(v.check_out_date, '9999-12-31')
 group by a.report_date, a.apples_consumed
 order by a.report_date;

report_date
visitors_count
apples_consumed

2022-01-01
2
5

2022-02-01
3
7

2022-03-01
3
2

SELECT 3