MySQL 8.0

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


version()


8.0.26


CREATE TABLE listings
(
  id INTEGER NOT NULL PRIMARY KEY,
  status VARCHAR (50),
  added_date datetime,
  updated_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  refno VARCHAR (50)
);
CREATE TABLE logs
(
  id INTEGER NOT NULL PRIMARY KEY,
  status_from VARCHAR (50),
  status_to  VARCHAR (50),
  logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  refno VARCHAR (50)
);
INSERT INTO listings VALUES
 (3, 'Y','2021-05-02','2021-10-02','LP01'), (4, 'A','2021-05-01','2021-05-01','LP02'),
 (5, 'S','2020-10-01','2020-10-01','LP03'), (6, 'N','2021-05-01','2021-10-06','LP06'), 
 (10, 'D','2021-10-06','2021-10-06','LP05'), (11, 'D','2021-01-01','2021-01-01','LP04');
INSERT INTO logs VALUES
 (1, 'Let','Action','2021-06-01','LP01'), (2, 'Action','Draft','2021-10-01','LP01'), 
 (3, 'Draft','Publish','2021-10-02','LP01'), (4, 'Action','Let','2021-10-06','LP06');
SELECT refno, logtime, status_from, status_to FROM (
SELECT refno, logtime, status_from, status_to, ROW_NUMBER() OVER(PARTITION BY refno ORDER BY logtime DESC) AS RN
FROM logs
WHERE logtime < '2021-10-12 00:00:00'
) r
WHERE r.RN = 1
UNION
SELECT refno, logtime, status_from, status_to
FROM logs
WHERE logtime <= '2021-10-12 00:00:00' AND logtime >= '2015-10-02 00:00:00'  
ORDER BY `refno` ASC


refno
logtime
status_from
status_to


LP01
2021-10-02 00:00:00
Draft
Publish

LP01
2021-06-01 00:00:00
Let
Action

LP01
2021-10-01 00:00:00
Action
Draft

LP06
2021-10-06 00:00:00
Action
Let


select
case status
when 'D' THEN 'Draft'
when 'A' THEN 'Action'
when 'Y' THEN 'Publish'
when 'S' THEN 'Sold'
when 'N' THEN 'Let'
END status_l ,COUNT(*) c
from listings
group by status


status_l
c


Publish
1

Action
1

Sold
1

Let
1

Draft
2


select * from logs


id
status_from
status_to
logtime
refno


1
Let
Action
2021-06-01 00:00:00
LP01

2
Action
Draft
2021-10-01 00:00:00
LP01

3
Draft
Publish
2021-10-02 00:00:00
LP01

4
Action
Let
2021-10-06 00:00:00
LP06


select status_logs, sum(cnt_status) to_add from (
SELECT 
status_to as status_logs, -1*count(*) cnt_status
FROM logs lm
where
id = (select max(id) from logs l where l.refno = lm.refno) and 
logtime >= '2021-10-02 00:00:00'
group by status_to
union all 
SELECT 
status_from, count(*) cnt_status_from
FROM logs lm
where
id = (select max(id) from logs l where l.refno = lm.refno) and
logtime >= '2021-10-02 00:00:00'
group by status_from ) total_changes
group by status_logs



status_logs
to_add


Publish
-1

Let
-1

Draft
1

Action
1


select distinct  IFNULL(status_l,status_logs) status ,counts_at_2021_10_01 from (

select l.*,logs.* , l.c + ifnull(logs.to_add,0) counts_at_2021_10_01 from
(select
case status
when 'D' THEN 'Draft'
when 'A' THEN 'Action'
when 'Y' THEN 'Publish'
when 'S' THEN 'Sold'
when 'N' THEN 'Let'
END status_l ,COUNT(*) c
from listings
group by status) l left join (

select status_logs, sum(cnt_status) to_add from (
SELECT 
status_to as status_logs, -1*count(*) cnt_status
FROM logs lm
where
id = (select max(id) from logs l where l.refno = lm.refno) and 
logtime >= '2021-10-01 00:00:00'
group by status_to
union all 
SELECT 
status_from, count(*) cnt_status_from
FROM logs lm
where
id = (select max(id) from logs l where l.refno = lm.refno) and
logtime >= '2021-10-01 00:00:00'
group by status_from ) total_changes
group by status_logs


) logs on logs.status_logs = l.status_l

union all


select l.*,logs.* , l.c + ifnull(logs.to_add,0) counts_at_2021_05_01 from
(select
case status
when 'D' THEN 'Draft'
when 'A' THEN 'Action'
when 'Y' THEN 'Publish'
when 'S' THEN 'Sold'
when 'N' THEN 'Let'
END status_l ,COUNT(*) c
from listings
group by status) l right join (

select status_logs, sum(cnt_status) to_add from (
SELECT 
status_to as status_logs, -1*count(*) cnt_status
FROM logs lm
where
id = (select max(id) from logs l where l.refno = lm.refno) and 
logtime >= '2021-10-01 00:00:00'
group by status_to
union all 
SELECT 
status_from, count(*) cnt_status_from
FROM logs lm
where
id = (select max(id) from logs l where l.refno = lm.refno) and
logtime >= '2021-10-01 00:00:00'
group by status_from ) total_changes
group by status_logs

) logs on logs.status_logs = l.status_l

) l


status
counts_at_2021_10_01


Publish
0

Action
2

Sold
1

Let
0

Draft
3
refno	logtime	status_from	status_to
LP01	2021-10-02 00:00:00	Draft	Publish
LP01	2021-06-01 00:00:00	Let	Action
LP01	2021-10-01 00:00:00	Action	Draft
LP06	2021-10-06 00:00:00	Action	Let