clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799161 fiddles created (41763 in the last week).

select version();
version()
8.0.27
 hidden batch(es)


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) );
 hidden batch(es)


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) );
 hidden batch(es)


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') ;
 hidden batch(es)


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') ;
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)