Study of MFTs - Rahil

set @start := '2019-1-1';

select
A.mid,
B.rid,
B.email,
I.age,
A.date as MFTdate,
B.date as Createddate,
A.week,
I.resv_hour,
I.occupy_start_hour,
count(A.mid) as totalmfts,
B.dur,
B.carname,
IFNULL(B.city,"") as City,
IFNULL(B.region,"") as Region,
IFNULL(B.zid,"") as zoneid,
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,
G.referral,
IFNULL(H.comment,"") as couponname,
IFNULL(B.referral,"") as Referral,
I.way,
I.state,
I.resv_Date,
timestampdiff(day, B.date, I.resv_Date) as Signup_to_MFT_datediff
from
(select
distinct c.member_id as mid,
date(c.created_at + interval '8' hour) as date,
WEEKOFYEAR(c.created_at + interval '8' hour) as Week
from charges c
where c.kind = 'subscriptionFee'
and c.created_at + interval '8' hour >= @start
group by 1) A
join
(select distinct r.member_id as mid,
min(r.id) as rid
from reservations r
group by 1
order by r.member_id Asc) L
on L.mid = A.mid
left join
(select
distinct r.id as rid,
date(m.created_at + interval '8' hour) as date,
m.email as email,
ma.recommender as referral,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
z.city as city,
z.region as region,
z.id as zid,
cc.car_name as carname
from reservations r
join members m on r.member_id = m.id
join member_appendixes ma on ma.member_id = m.id
join zones z on r.start_zone_id = z.id
join cars ca on ca.id = r.car_id
join car_classes cc on cc.id = ca.car_class_id
where m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.start_at + interval '8' hour >= @start
group by 1) B
on L.rid = B.rid
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') and c.created_at + interval '8' hour >= @start
group by rid) E on B.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 F.rid = E.rid
left join
(select
distinct ma.member_id as mid,
count(distinct case when ma.recommender like '%@%' then ma.member_id end) as referral
from member_appendixes ma
group by 1) G
on G.mid = A.mid
left join
(select
Distinct c.reservation_id as rid,
c.comment as comment
from coupons c) H
on H.rid = B.rid
left join
(select distinct r.id as rid,
r.state as state,
r.way as way,
Date(r.start_at + interval '8' hour) as resv_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.occupy_start_at) - YEAR(m.birthday)) as age
from reservations r
join members m on m.id = r.member_id) I
on I.rid = B.rid
where B.city is not null
group by A.mid