Doc Reject - card approved - Rahil

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

select
(A.month) as month,
count(A.mid) as Doc_rejected,
count(B.mid) as Debit_Card_Approved,
round((count(B.mid)/count(A.mid)*100),2) as PCT_Valid_DC_Doc_rejected
from
(select
dl.member_id as mid,
month(dl.created_at + interval '8' hour) as month
from driver_licenses dl
join members m
on m.id = dl.member_id
where dl.created_at + interval '8' hour >= @start
and dl.state = 'reject'
and m.imaginary = 'normal'
group by 1) A
left join
(select
pm.member_id as mid,
month(pm.created_at + interval '8' hour) as month
from
payment_methods as pm
where pm.created_at + interval '8' hour >= @start
and pm.state = 'approved'
group by 1) B
on A.mid = B.mid
group by 1