sql.txt

SELECT organizations.name, 
       organizations.id, 
       organizations.ancestry, 
       tmp.average_activated_time_of_children from organizations 
       JOIN (
         SELECT org.ancestry, 
                AVG(org.updated_at - org.created_at) as average_activated_time_of_children, 
                CAST(substring(org.ancestry from '\d+(?!\/)$') as integer) as organization_id  
         FROM organizations org 
         INNER JOIN accounts acc ON acc.organization_id=org.id 
         WHERE org.ancestry != '1' 
         AND acc.status = 'CANCELED' 
         GROUP BY org.ancestry) as tmp ON organizations.id = tmp.organization_id;