Cohort analyses - MFT_21

select

A.memberid as memberid,
A.age as age,
Date(A.MFTDate) as MFTdate,
date(A.lastactivedate) as lastactivedate

from

(select
m.id as memberid,
TIMESTAMPDIFF(YEAR, str_to_date(m.birthday, '%Y%m%d'), Now()) AS age,
min(r.start_at) as MFTdate,
Max(r.start_at) as lastactivedate

from  members m

left outer join reservations r
on m.id = r.member_id

join charges c
on m.id = c.member_id

Where str_to_date(m.birthday, '%Y%m%d') >= NOW() - interval '21' year
and r.start_at != 0
and r.start_at >= '2019-3-1 00:00'
and m.imaginary = 'normal'
and c.kind = 'subscriptionFee'


group by m.id) A