Test Queries for Candidates - Rahil

set @startdate1 = '2019-02-01';
set @startdate2 = '2019-03-01';
use socar_malaysia;
select  A.rid2 as rid, A.mid as mid,  IFNULL(E.charges,0) as gross_rev from
(select
distinct r.id as rid2, r.member_id as mid, z.region, z.city, cc.car_name,IFNULL(co.comment,"") as coupon,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
IFNULL(sum(ra.mileage),0) as distance,
Date(r.return_at + interval '8' hour) as return_Date,
hour(r.created_at + interval '8' hour) as created_hour,
Date(r.start_at + interval '8' hour) as start_Date,
hour(r.start_at + interval '8' hour) as resv_hour,
hour(r.occupy_start_at + interval '8' hour) as occupy_start_hour,
(YEAR(r.start_at) - YEAR(m.birthday)) as age
from reservations r left join members m on r.member_id = m.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
left join coupons co on co.reservation_id = r.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
group by rid2) 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','insurance')
group by rid) E on A.rid2 = 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.rid2 = F.rid
order by 1 asc;


set @startdate1 = '2019-02-01';
set @startdate2 = '2019-03-01';
use socar_malaysia;
select  A.rid2 as rid, A.mid as mid,  IFNULL(F.coupon_Spent,0) as Coupon_Spent
 from
(select
distinct r.id as rid2, r.member_id as mid, z.region, z.city, cc.car_name,IFNULL(co.comment,"") as coupon,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
IFNULL(sum(ra.mileage),0) as distance,
Date(r.return_at + interval '8' hour) as return_Date,
hour(r.created_at + interval '8' hour) as created_hour,
Date(r.start_at + interval '8' hour) as start_Date,
hour(r.start_at + interval '8' hour) as resv_hour,
hour(r.occupy_start_at + interval '8' hour) as occupy_start_hour,
(YEAR(r.start_at) - YEAR(m.birthday)) as age
from reservations r left join members m on r.member_id = m.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
left join coupons co on co.reservation_id = r.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
group by rid2) 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','insurance')
group by rid) E on A.rid2 = 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.rid2 = F.rid
where IFNULL(F.coupon_Spent,0) > 0
order by 1 desc;


set @startdate1 = '2019-02-01';
set @startdate2 = '2019-03-01';
use socar_malaysia;
select A.return_date as return_date, A.resv_hour as resv_Start_hour, A.rid2 as rid, A.mid as mid, A.region as region, A.city as city, A.car_name as car_name,
A.coupon as coupon, A.dur as dur, A.distance as distance, A.age as age from
(select
distinct r.id as rid2, r.member_id as mid, z.region, z.city, cc.car_name,IFNULL(co.comment,"") as coupon,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
IFNULL(sum(ra.mileage),0) as distance,
Date(r.return_at + interval '8' hour) as return_Date,
hour(r.created_at + interval '8' hour) as created_hour,
Date(r.start_at + interval '8' hour) as start_Date,
hour(r.start_at + interval '8' hour) as resv_hour,
hour(r.occupy_start_at + interval '8' hour) as occupy_start_hour,
(YEAR(r.start_at) - YEAR(m.birthday)) as age
from reservations r left join members m on r.member_id = m.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
left join coupons co on co.reservation_id = r.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
group by rid2) 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','insurance')
group by rid) E on A.rid2 = 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.rid2 = F.rid
order by 1 desc