Skip to content

Instantly share code, notes, and snippets.

@rewinfrey
Created March 13, 2015 23:11
Show Gist options
  • Save rewinfrey/bab43a411d702dccd35a to your computer and use it in GitHub Desktop.
Save rewinfrey/bab43a411d702dccd35a to your computer and use it in GitHub Desktop.
sql with negative look ahead
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