Ids to Check Braze - Rahil

select A.mid  as mid,
A.member_created_date as member_created_date
from
(select distinct m.id as mid,
Date(m.created_at + interval 8 hour) as member_created_date,
count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
count(distinct case when pm.state = 'approved' then pm.member_id end) paymentupload,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) MFTs
from members m
left join driver_licenses dl on dl.member_id = m.id
left join payment_methods pm on pm.member_id = m.id
left join charges c on c.member_id = m.id
where Date(m.created_at + interval 8 hour) <= now() - interval 15 day
and Date(m.created_at + interval 8 hour) >= now() - interval 30 day
group by 1
order by 2 desc) A
where A.DocApproved = 1
and A.paymentupload = 1
and A.MFTs = 0
order by 2 desc;

select count(mid) as signup,
Sum(A.DocApproved) as DocApproved,
sum(A.paymentupload) as paymentupload,
Sum(A.MFTs) as MFTs
from
(select distinct m.id as mid,
Date(m.created_at + interval 8 hour) as member_created_date,
count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
count(distinct case when pm.state = 'approved' then pm.member_id end) paymentupload,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) MFTs
from members m
left join driver_licenses dl on dl.member_id = m.id
left join payment_methods pm on pm.member_id = m.id
left join charges c on c.member_id = m.id
where Date(m.created_at + interval 8 hour) <= now() - interval 15 day
and Date(m.created_at + interval 8 hour) >= now() - interval 30 day
group by 1
order by 2 desc) A
 ;

 select count(mid) as signup,
Sum(A.DocApproved) as DocApproved,
sum(A.paymentupload) as paymentupload,
count(A.MFTs) as MFTs
from
(select distinct m.id as mid,
Date(m.created_at + interval 8 hour) as member_created_date,
count(distinct case when dl.state = 'approved' then dl.member_id end) DocApproved,
count(distinct case when pm.state = 'approved' then pm.member_id end) paymentupload,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) MFTs
from members m
left join driver_licenses dl on dl.member_id = m.id
left join payment_methods pm on pm.member_id = m.id
left join charges c on c.member_id = m.id
where Date(m.created_at + interval 8 hour) <= now() - interval 15 day
and Date(m.created_at + interval 8 hour) >= now() - interval 30 day
group by 1
order by 2 desc) A
where A.DocApproved = 1
and A.paymentupload = 1
and A.MFTs = 0