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;
