stale_orgs.sql

-- Orgs with no logins in 90 days
select organizations.id, 
       organizations.name,
       to_char(max(security_users.last_sign_in_at), 'MM/DD/YYYY') last_sign_at, 
       to_char(organizations.created_at, 'MM/DD/YYYY') created_at,
       count(*) num_users
from organizations left outer join security_users on (organizations.id = security_users.organization_id)
where organizations.created_at < now() - interval '90 days' and organizations.disabled = false
group by organizations.id, organizations.name
having max(security_users.last_sign_in_at) is null or age(now(), max(security_users.last_sign_in_at)) > interval '90 days'
order by max(security_users.last_sign_in_at) nulls first, organizations.created_at;

stale_orgs_csv.sql

\copy (select organizations.id, organizations.name, count(*) num_users, to_char(max(security_users.last_sign_in_at), 'MM/DD/YYYY') last_sign_at, to_char(organizations.created_at, 'MM/DD/YYYY') created_at from organizations left outer join security_users on (organizations.id = security_users.organization_id) where organizations.created_at < now() - interval '90 days' and organizations.disabled = false group by organizations.id, organizations.name having max(security_users.last_sign_in_at) is null or age(now(), max(security_users.last_sign_in_at)) > interval '90 days' order by organizations.id) To '/Users/jturkel/Documents/old_orgs.csv' With CSV