Circs by Author

-- some ineffecient stuff to move things to temp tables simply because our main tables are so large now
-- these reports give me a analysis for a library system of what their highest circulating bib records and then authors are

-- I need a copy location crib sheet

select * from asset.copy_location acl
join actor.org_unit child on child.id = acl.owning_lib
join actor.org_unit parent on parent.id = child.parent_ou
where parent.shortname = 'YCL'
;

-- YCL's are named consistently so I'm going to filter by copy location name
-- if the systems were not named consistently I would cheat and map them, take out everything that's not adult and not fiction

create table rogan.ycl_circs as
select circ.*
from action.circulation circ
join asset.copy ac on ac.id = circ.target_copy
join asset.call_number call on call.id = ac.call_number
join actor.org_unit child on child.id = circ.circ_lib
join actor.org_unit parent on parent.id = child.parent_ou
join asset.copy_location acl on acl.id = ac.location
where parent.shortname = 'YCL' and date(circ.xact_start) >= '2013-01-01' and date(circ.xact_start) <= '2013-12-31'
and acl.name like 'Adult%' and acl.name not ilike '%DVD%' and acl.name not ilike '%non%' and acl.name not ilike '%periodical%' and acl.name not ilike '%books on%'
;

-- now I need to find the bibs of each item

create table rogan.ycl_authors as
select circ.id as "circ_id", ac.id as "copy_id", call.id as "call_id", msr.id as "bib_id", msr.title, msr.author
from rogan.ycl_circs circ
join asset.copy ac on ac.id = circ.target_copy
join asset.call_number call on call.id = ac.call_number
join reporter.materialized_simple_record msr on msr.id = call.record
;

-- now let's get counts of them

create table rogan.ycl2013 as
select count(circ.id), ycl.author, ycl.title
from rogan.ycl_circs circ
join rogan.ycl_authors ycl on ycl.copy_id = circ.target_copy
group by 2, 3
having count(circ.id) > 24
;

drop table rogan.ycl_authors;
drop table rogan.ycl_circs;

-- clean out the videos

delete from rogan.ycl2013 where author is null;

create table rogan.ycl2013_authors as
select sum(ycl.count) as "circ_count", ycl.author
from rogan.ycl2013 ycl
group by 2
;

select count(circ_count) from rogan.ycl2013_authors where circ_count >= 75;

select * from rogan.ycl2013_authors order by 2 ASC;

select * from rogan.ycl2013_authors order by 1 DESC limit 10;


-- now I need to get rid of those that are juvenile only