Cars added in a month / week - Rahil

set @start := '2018-12-31 00:00';

Select
t1.date,
    t1.ncars,
    t1.ncars - COALESCE(t2.ncars, t1.ncars) AS diff
from

(select
Date(date_sub(czl.log_date , interval 1 day)) as date,
weekday(date_sub(czl.log_date , interval 1 day)) as weekday,
count(distinct czl.car_id ) as ncars
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and weekday(date_sub(czl.log_date , interval 1 day)) = 6
group by 1) t1

left join

(select
Date(date_add(czl.log_date , interval 7 day)) as date,
weekday(date_add(czl.log_date , interval 7 day)) as weekday,
count(distinct czl.car_id ) as ncars
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and weekday(date_sub(czl.log_date , interval 1 day)) = 6
group by 1) t2

on t1.date   = t2.date - 1

order by t1.date ;


set @start := '2018-12-31 00:00';

Select
t1.date,
    t1.ncars,
    t1.ncars - COALESCE(t2.ncars, t1.ncars) AS diff
from

(select
Month(date_sub(czl.log_date , interval 1 day)) as date,
LAST_DAY(date_sub(czl.log_date , interval 1 day)) as weekday,
count(distinct czl.car_id ) as ncars
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and czl.log_date = LAST_DAY(date_sub(czl.log_date , interval 1 day))
group by 1) t1

left join

(select
Month(date_add(czl.log_date , interval 1 month)) as date,
LAST_DAY(date_add(czl.log_date , interval 1 month)) as weekday,
count(distinct czl.car_id ) as ncars
from car_zone_logs czl
where czl.log_date + interval '8' hour >= @start
and czl.car_state = 'normal'
and czl.zone_state = 'normal'
and czl.csa_state = 'normal'
and czl.log_date = LAST_DAY(date_sub(czl.log_date , interval 1 day))
group by 1) t2

on t1.date = t2.date

order by t1.date