Location Wise - SIGN UP to MFT Info

select

A.mid,
IFNULL(C.locality,0) as location,
IFNULL(A.signupdate,0) as signupdate,
IFNULL(B.docuploaddate,0) as docuploaddate,
IFNULL(D.carduploaddate,0) as carduploaddate,
IFNULL(E.MFTData,0) as MFTData,
IFNULL(A.signups,0) as signups,
IFNULL(B.DocUploaded,0) as DocUploaded,
IFNULL(B.DocApproved,0) as DocApproved,
IFNULL(D.cardupload,0) as cardupload,
IFNULL(E.MFTs,0) as MFTs
from
(select
distinct m.id as mid,
Date(m.created_at + interval '8' hour) as signupdate,
count(distinct m.id) as signups
from members m
where m.imaginary = 'normal'
and m.created_at > '2019-4-1'
group by 1) A
left join
(select
distinct ma.member_id as mid,
ma.domain_locality as locality
from member_appendixes ma
group by 1) C
on A.mid = C.mid
left join
(select
distinct dl.member_id as mid,
date(dl.created_at + interval '8' hour) as docuploaddate,
count(distinct dl.member_id) as DocUploaded,
count(distinct case when dl.state = 'approved' then dl.member_id end) as DocApproved
from driver_licenses dl
group by 1) B

on A.mid = B.mid

left join

(select
distinct pm.member_id as mid,
Date(pm.created_at + interval '8' hour) as carduploaddate,
count(distinct pm.member_id) as cardupload
from
payment_methods as pm
where pm.state = 'approved'
group by 1) D

on D.mid = A.mid

left join

(select
distinct c.member_id as mid,
Date(c.created_at + interval '8' hour) as MFTData,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) as MFTs
from charges c
group by 1) E

on A.mid = E.mid