create temporary table ejegg.recurfails as (
select contact_id, count(*) as fail_email_count
from civicrm_activity_contact ac
inner join civicrm_activity a on ac.activity_id = a.id
where activity_date_time > '2020-11-24'
and activity_type_id=3
and record_type_id=3
and subject like 'Recur fail %'
group by contact_id
);
CREATE table ejegg.T269372 AS (
select
rf.contact_id,
e.email,
c.first_name,
c.last_name,
c.preferred_language,
rf.fail_email_count,
count(cr3.id) as cancelled,
count(cr5.id) as inprogress,
count(cr15.id) as failing,
max(cr5.create_date) as last_signup
from ejegg.recurfails rf
left join civicrm.civicrm_contribution_recur cr3 on rf.contact_id = cr3.contact_id and cr3.contribution_status_id=3 and cr3.cancel_date > '2020-11-24'
left join civicrm.civicrm_contribution_recur cr5 on rf.contact_id=cr5.contact_id and cr5.contribution_status_id=5
left join civicrm.civicrm_contribution_recur cr15 on rf.contact_id=cr15.contact_id and cr15.contribution_status_id=15
left join civicrm.civicrm_email e on rf.contact_id = e.contact_id and e.is_primary=1
left join civicrm.civicrm_contact c on rf.contact_id = c.id
group by rf.contact_id, e.email, c.first_name, c.last_name, c.preferred_language
);
select * from ejegg.T269372 where last_signup > '2020-11-24' and inprogress > 1;