Catalog use along with circulation use for patrons

catalog logins - circs.sql

-- Stats on catalog logins compared with checkouts
select
aou.shortname, pgt.name, count(au.id) as "Total Users"
,count(ac2.id) as "Has Checked Out Since Migration"
,round((count(ac2.id)*100)::numeric/count(au.id),2)||'%' as percent1
,count(aua.id) as "OPAC Login since migration"
-- ,round((count(aua.id)*100)::numeric/count(ac2.id),2)||'%' as percent2
,count(ac1.id) as "Checkout in last month"
,count(aua2.id) as "OPAC Login in last month"
from

actor.usr au
join actor.org_unit aou on au.home_ou=aou.id
join permission.grp_tree pgt on pgt.id=au.profile
left outer join (select distinct on (aua.usr) * from actor.usr_activity aua where aua.etype=17 order by aua.usr, aua.event_time desc) aua
on aua.usr=au.id
left outer join (select distinct on (aua.usr) * from actor.usr_activity aua where aua.etype=17 and aua.event_time > now()-'1 month'::interval order by aua.usr, aua.event_time desc) aua2
on aua2.usr=au.id
left outer join (select distinct on (ac.usr) * from action.circulation ac where ac.xact_start > now()-'1 month'::interval order by ac.usr,ac.xact_start desc) ac1
on ac1.usr=au.id
left outer join (select distinct on (ac.usr) * from action.circulation ac where ac.xact_start > '2015-10-24'::timestamp order by ac.usr,ac.xact_start desc) ac2
on ac2.usr=au.id

where
au.deleted=false
and
au.active=true
-- and aua.id is not null
-- and
-- exists (select id from actor.usr_activity aua where aua.usr=au.id and aua.etype=17 order by aua.event_time desc limit 1)
group by aou.shortname, aou.parent_ou, pgt.name, pgt.id
order by aou.parent_ou, aou.shortname, pgt.name
-- limit 10