Weekly MFTs Journey -Rahil - Type by Week

select A.mid, A.week, IFNULL(B.resv,1) as Lsf6,IFNULL(E.gross,0) as gross,IFNULL(F.coupon_spent,0) as coupon from
(select
distinct c.member_id as mid,
weekofyear(c.created_at + interval '8' hour) as week
from charges c
where c.kind = 'subscriptionFee'
and Weekofyear(c.created_at + interval 8 hour) = '25'
and Year(c.created_at + interval 8 hour) = '2019') A
left join
(select
distinct r.member_id as mid,
weekofyear(r.start_at + interval '8' hour) as week,
count(r.id) as resv
from reservations r left join members m on m.id = r.member_id
where weekofyear(r.start_at + interval '8' hour) = '25'
and Year(r.start_at + interval 8 hour) = '2019'
and r.state = 'completed'
and m.state = 'normal'
and m.imaginary = 'normal'
group by r.member_id, week) B on A.mid = B.mid
left join
(select c.member_id as mid, weekofyear(c.created_at + interval '8' hour) as week, sum(c.amount) as gross from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage') and c.created_at + interval '8' hour >= @start and weekofyear(c.created_at + interval '8' hour) = '25'
group by mid,week) E on A.mid = E.mid
left join
(select p.member_id as mid, weekofyear(p.created_at + interval '8' hour) as week, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon' and weekofyear(p.created_at + interval '8' hour) = '25'
group by mid,week) F on F.mid = A.mid