1.sql

UPDATE accounts d
      SET user_ids = user_ids_aggregated_value, 
user_count = user_count_aggregated_value, 
reported_user_count = reported_user_count_aggregated_value, 
client_count = client_count_aggregated_value, 
member_count = member_count_aggregated_value, 
lead_member_count = lead_member_count_aggregated_value, 
consultation_count = consultation_count_aggregated_value, 
reported_consultation_count = reported_consultation_count_aggregated_value, 
active_project_count = active_project_count_aggregated_value, 
project_count = project_count_aggregated_value, 
total_revenue = total_revenue_aggregated_value, 
total_credits = total_credits_aggregated_value, 
updated_at = greatest(member_count_aggregated_updated_at, lead_member_count_aggregated_updated_at, total_revenue_aggregated_updated_at, updated_at)
      FROM (
        SELECT id, user_ids_aggregated.value user_ids_aggregated_value, user_count_aggregated.value user_count_aggregated_value, reported_user_count_aggregated.value reported_user_count_aggregated_value, client_count_aggregated.value client_count_aggregated_value, member_count_aggregated.updated_at member_count_aggregated_updated_at, member_count_aggregated.value member_count_aggregated_value, lead_member_count_aggregated.updated_at lead_member_count_aggregated_updated_at, lead_member_count_aggregated.value lead_member_count_aggregated_value, consultation_count_aggregated.value consultation_count_aggregated_value, reported_consultation_count_aggregated.value reported_consultation_count_aggregated_value, active_project_count_aggregated.value active_project_count_aggregated_value, project_count_aggregated.value project_count_aggregated_value, total_revenue_aggregated.updated_at total_revenue_aggregated_updated_at, total_revenue_aggregated.value total_revenue_aggregated_value, total_credits_aggregated.value total_credits_aggregated_value
        FROM accounts
        LEFT JOIN (SELECT account_id as id, array_agg(distinct creator_id) as value from consultations WHERE account_id = ANY(ids) AND creator_id is not null GROUP BY account_id) user_ids_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, count(distinct creator_id) as value from consultations WHERE account_id = ANY(ids) AND creator_id is not null GROUP BY account_id) user_count_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, count(distinct creator_id) as value from reported_consultations WHERE account_id = ANY(ids) AND creator_id is not null GROUP BY account_id) reported_user_count_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, count(1) as value from clients WHERE account_id = ANY(ids) GROUP BY account_id) client_count_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, count(1) as value, max(project_memberships.updated_at) as updated_at from project_memberships LEFT JOIN projects ON projects.id = project_id WHERE account_id = ANY(ids) AND status='approved' OR status='selected' GROUP BY account_id) member_count_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, count(1) as value, max(project_memberships.updated_at) as updated_at from project_memberships LEFT JOIN projects ON projects.id = project_id WHERE account_id = ANY(ids) GROUP BY account_id) lead_member_count_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, count(1) as value from consultations WHERE account_id = ANY(ids) GROUP BY account_id ) consultation_count_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, count(1) as value from reported_consultations WHERE account_id = ANY(ids) GROUP BY account_id) reported_consultation_count_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, count(distinct project_id) as value from reported_consultations WHERE account_id = ANY(ids) GROUP BY account_id) active_project_count_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, count(id) as value from reported_projects WHERE account_id = ANY(ids) GROUP BY account_id) project_count_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, sum(consultation_invoices.amount) as value, max(consultation_invoices.updated_at) as updated_at from reported_consultations LEFT JOIN consultation_invoices on consultation_invoices.consultation_id = reported_consultations.id WHERE account_id = ANY(ids) GROUP BY account_id) total_revenue_aggregated USING(id)      
LEFT JOIN (SELECT account_id as id, sum(accounted_consultations.credits) as value from accounted_consultations WHERE account_id = ANY(ids) GROUP BY 1) total_credits_aggregated USING(id)        -- aggregate each counter for join
        WHERE accounts.id = ANY(ids) and (accounts.user_ids IS DISTINCT FROM user_ids_aggregated.value OR 
accounts.user_count IS DISTINCT FROM user_count_aggregated.value OR 
accounts.reported_user_count IS DISTINCT FROM reported_user_count_aggregated.value OR 
accounts.client_count IS DISTINCT FROM client_count_aggregated.value OR 
accounts.member_count IS DISTINCT FROM member_count_aggregated.value OR 
accounts.lead_member_count IS DISTINCT FROM lead_member_count_aggregated.value OR 
accounts.consultation_count IS DISTINCT FROM consultation_count_aggregated.value OR 
accounts.reported_consultation_count IS DISTINCT FROM reported_consultation_count_aggregated.value OR 
accounts.active_project_count IS DISTINCT FROM active_project_count_aggregated.value OR 
accounts.project_count IS DISTINCT FROM project_count_aggregated.value OR 
accounts.total_revenue IS DISTINCT FROM total_revenue_aggregated.value OR 
accounts.total_credits IS DISTINCT FROM total_credits_aggregated.value)      -- avoid recursion on cyclic aggregates
      ) q 
      WHERE d.id = q.id
      RETURNING d.id