TTT - Host acquisition Queries - 90 days Inactive

#### One way

Select distinct r.member_id as mid, m.display_name as name, m.email as email from reservations r
join members m on m.id = r.member_id
where r.way in ('oneway','onewayReturn')
and m.imaginary in ('normal')
order by 1 desc;

#### last 90 Days inactive and only one reservation

select A.mid as mid , A.name as name ,A.email as email from
(Select distinct r.member_id as mid, count(r.id) as reservations, max(Date(r.start_at + interval 8 hour)) as last_resv_date,
m.display_name as name, m.email as email from reservations r
join members m on m.id = r.member_id
where r.state = 'completed'
and m.imaginary in ('normal')
group by 1 desc
order by 1 desc) A
where A.reservations = '1'
and A.last_resv_date <= date_sub(CURDATE(), interval 90 day) ;

#### No Reservation but Docs + Payment Approved

select A.mid as mid, A.name as name, A.email as email from
(select distinct m.id as mid , m.display_name as name, m.email as email,
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) cardupload,
count(r.id) as reservations,
max(Date(r.start_at + interval 8 hour)) as last_resv_date
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 reservations r on m.id = r.member_id
where m.imaginary in ('normal')
group by 1
order by 1 desc ) A
where A.reservations = 0
and A.DocApproved = 1
and A.cardupload = 1
#and A.last_resv_date <= date_sub(CURDATE(), interval 90 day);