prediction_engagement_email.sql

SELECT 
    agent_id,
    agent_type,
    is_owner,
    agent_name,
    agent_email,
    subquery.*
FROM
    (SELECT 
        account_id,
            run_at,
            subdomain,
            SUM(satisfaction_prediction_enabled) satisfaction_prediction_enabled,
            SUM(satisfaction_ratings_last_90_days) satisfaction_ratings_last_90_days,
            SUM(views_using_satisfaction_probability) views_using_satisfaction_probability,
            SUM(business_rules_using_satisfaction_probability) business_rules_using_satisfaction_probability
    FROM
        accounts a
    INNER JOIN accounts_settings s USING (account_id , run_at)
    INNER JOIN satisfaction_prediction p USING (account_id , run_at)
    WHERE
        a.run_at = (SELECT 
                MAX(run_at)
            FROM
                accounts)
            AND a.is_owner_zendesk = 0
            AND a.plan_type = 4
    GROUP BY account_id
    HAVING satisfaction_ratings_last_90_days > 1000) sub_query
        INNER JOIN
    agent_email_addresses d USING (account_id , run_at)

Updated to include "Admins" only, locale is English default and fixed the sub_query reference:

SELECT 
    agent_id,
    agent_type,
    is_owner,
    agent_name,
    agent_email,
    sub_query.*
FROM
    (SELECT 
        account_id,
            run_at,
            subdomain,
            SUM(satisfaction_prediction_enabled) satisfaction_prediction_enabled,
            SUM(satisfaction_ratings_last_90_days) satisfaction_ratings_last_90_days,
            SUM(views_using_satisfaction_probability) views_using_satisfaction_probability,
            SUM(business_rules_using_satisfaction_probability) business_rules_using_satisfaction_probability
    FROM
        accounts a
    INNER JOIN accounts_settings s USING (account_id , run_at)
    INNER JOIN satisfaction_prediction p USING (account_id , run_at)
    WHERE
        a.run_at = (SELECT 
                MAX(run_at)
            FROM
                accounts)
            AND a.is_owner_zendesk = 0
            AND a.plan_type = 4
            AND a.locale_id = 1
    GROUP BY account_id
    HAVING satisfaction_ratings_last_90_days > 1000) sub_query
        INNER JOIN
    agent_email_addresses d USING (account_id , run_at) WHERE agent_type = "Admin"