Gale Analytics - Marketing report

drop table if exists rogan.gale_marketing;

create table rogan.gale_marketing as
select au.id, aua.street1, aua.street2, aua.city, aua.state, aua.post_code as zip
from actor.usr au
join actor.usr_address aua on aua.id = au.mailing_address
join actor.org_unit child_org on child_org.id = au.home_ou
join actor.org_unit parent_org on parent_org.id = child_org.parent_ou
where parent_org.shortname = 'YCL'  and au.deleted = false and au.active = true
and au.expire_date > now() - interval '2 years'
-- and (aua.post_code ilike '297%' or aua.post_code ilike '28%')
and aua.county = 'York' and aua.post_code != '' and street1 != ''
group by 1, 2, 3, 4, 5, 6
;

alter table rogan.gale_marketing add column event_time timestamptz;
alter table rogan.gale_marketing add column circ_time timestamptz;

update rogan.gale_marketing set event_time = r.ret
from ( select rgm.id as rd, max(aua.event_time) as ret
	from rogan.gale_marketing rgm
	join actor.usr_activity aua on aua.usr = rgm.id
	group by 1) r
where r.rd = id ;

update rogan.gale_marketing set circ_time = r.ret
from ( select rgm.id as rd, max(acirc.create_time) as ret
	from rogan.gale_marketing rgm
	join action.circulation acirc on acirc.usr = rgm.id
	group by 1) r
where r.rd = id ;



select * from rogan.gale_marketing limit 10;

select street1 as address, street2, city, state, zip, date(greatest(circ_time,event_time)) as "date"
from rogan.gale_marketing
;