Skip to content

Instantly share code, notes, and snippets.

@pzula
Forked from rewinfrey/sql.txt
Last active August 29, 2015 14:17
Show Gist options
  • Save pzula/8677d00a49f370b03787 to your computer and use it in GitHub Desktop.
Save pzula/8677d00a49f370b03787 to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment