Study of Accidents - Rahil

set @startDate = "2019-01-01 00:00:00";

select A.date as date, A.rid as rid, A.mid as mid, A.age, A.resv_Start_hour as resv_Start_hour,A.dur as dur, A.region as region,
A.city as city, A.carname as carname, A.kind as kind,IFNULL(E.charges,0) as gross_rev,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev from

(select
distinct c.reservation_id as rid,
m.id as mid,
(YEAR(r.start_at) - YEAR(m.birthday)) as age,
date(c.created_at + interval '8' hour) as date,
hour(r.start_at + interval '8' hour) as resv_Start_hour,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
z.region as region, z.city as city, cc.car_name as carname,c.kind as kind
from charges c
left join members m on m.id = c.member_id
left join reservations r on r.id = c.reservation_id
left join zones z on z.id = r.start_zone_id
left join cars cr on cr.id = r.car_id
left join car_classes cc on cc.id = cr.car_class_id
where c.kind = 'accident'
and date(c.created_at + interval '8' hour) >= @startdate
group by 1) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage')
group by rid) E on A.rid = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid = F.rid
where A.rid is not null
group by 2