MySQL 8.0

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

select version();


version()


8.0.27


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),
  UNIQUE (refno),
  INDEX(added_date)
);
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),
  FOREIGN KEY (refno) REFERENCES listings(refno),
  unique (refno, logtime),
  INDEX(logtime)
);
INSERT INTO listings VALUES
 (1, 'Y','2021-05-02','2021-10-02','LP07'),
 (2, 'Y','2021-05-02','2021-10-02','LP08'),
 (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, 'N','A','2021-06-01','LP01'),
 (2, 'A','D','2021-10-01','LP01'),
 (3, 'D','Y','2021-10-02','LP01'),
 (4, 'A','N','2021-10-06','LP06'),
 (5, 'N','A','2021-10-02','LP07'),
 (6, 'A','D','2021-10-08','LP07'),
 (7, 'N','A','2021-10-03','LP08'),
 (8, 'A','L','2021-10-12','LP08')
;
analyze table listings;
analyze table logs;


Table
Op
Msg_type
Msg_text


db_2122573994.listings
analyze
status
OK




Table
Op
Msg_type
Msg_text


db_2122573994.logs
analyze
status
OK


with Y as (
  with recursive D (n, day) as (
    select 1 as n, '2021-09-25' my_date
    union
    select n+1, day + interval 1 day from D
      where day + interval 1 day < '2021-10-15'
  ) select * from D
), X as (
  select Y.day,
  l.status,
         (select status_from from logs
            where logs.refno = l.refno
              and logs.logtime >= Y.day
            order by logs.logtime
            limit 1) logstat
    from listings l, Y
    where l.added_date <= Y.day
), Z as (
  select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt
    from X
    group by X.day, stat_day
)
select Z.day,
  sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft,
  sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action,
  sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish,
  sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold,
  sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let
  from Z
  group by Z.day
  order by Z.day;


day
Draft
Action
Publish
Sold
Let


2021-09-25
1
3
0
1
0

2021-09-26
1
3
0
1
0

2021-09-27
1
3
0
1
0

2021-09-28
1
3
0
1
0

2021-09-29
1
3
0
1
0

2021-09-30
1
3
0
1
0

2021-10-01
1
3
0
1
0

2021-10-02
2
2
0
1
0

2021-10-03
1
3
1
1
0

2021-10-04
1
4
1
1
0

2021-10-05
1
4
1
1
0

2021-10-06
2
4
1
1
0

2021-10-07
2
3
1
1
0

2021-10-08
2
3
1
1
0

2021-10-09
2
2
2
1
0

2021-10-10
2
2
2
1
0

2021-10-11
2
2
2
1
0

2021-10-12
2
2
2
1
0

2021-10-13
2
1
3
1
0

2021-10-14
2
1
3
1
0

day	Draft	Action	Publish	Sold
2021-09-25	1	3	0	1
2021-09-26	1	3	0	1
2021-09-27	1	3	0	1
2021-09-28	1	3	0	1
2021-09-29	1	3	0	1
2021-09-30	1	3	0	1
2021-10-01	1	3	0	1
2021-10-02	2	2	0	1
2021-10-03	1	3	1	1
2021-10-04	1	4	1	1
2021-10-05	1	4	1	1
2021-10-06	2	4	1	1
2021-10-07	2	3	1	1
2021-10-08	2	3	1	1
2021-10-09	2	2	2	1
2021-10-10	2	2	2	1
2021-10-11	2	2	2	1
2021-10-12	2	2	2	1
2021-10-13	2	1	3	1
2021-10-14	2	1	3	1