Signup to Doc Approved - Rahil

set @start = '2020-01-01';

select
(A.month) as month,
sum(A.signups) as signups,
sum(B.DocUploaded) as DocUploaded,
sum(B.DocApproved) as DocApproved,
sum(B.DocRejected) as DocRejected,
sum(C.payment_approved) as payment_approved
from

(select
m.id as mid,
month(m.created_at + interval '8' hour) as month,
count(distinct m.id) as signups
from members m
where m.imaginary = 'normal'
and date(m.created_at + interval 8 hour) >= @start
group by 1) A
left join
(select
dl.member_id as mid,
month(dl.created_at + interval '8' hour) as month,
count(distinct case when dl.state not in ('noInput','null') then dl.member_id end) DocUploaded,
count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
count(distinct case when dl.state = 'reject' then dl.member_id end) DocRejected
from driver_licenses dl
join members m
on m.id = dl.member_id
where dl.created_at + interval '8' hour >= @start
and m.imaginary = 'normal'
group by 1) B
on A.mid = B.mid
left join
(select
pm.member_id as mid,
month(pm.created_at + interval '8' hour) as month,
count(distinct case when pm.state = 'approved' then pm.member_id end) payment_approved
from
payment_methods as pm
where pm.created_at + interval '8' hour >= @start
group by 1) C
on A.mid = C.mid
group by 1