Record Stats Query

-- Note: Assuming that the 6 months parts are pulled from a setting of some sort, and not hardcoded or anything.
SELECT
    -- For grouping and record identification
    bre.id,
    -- For use in activity metric work to be done later
    count(DISTINCT CASE WHEN p.relname = 'copy' THEN acp.id ELSE NULL END) AS copies,
    count(DISTINCT CASE WHEN p.relname = 'unit' THEN acp.id ELSE NULL END) AS serials,
    count(DISTINCT CASE WHEN acp.holdable AND acl.holdable AND ccs.holdable THEN acp.id ELSE NULL END) as holdable,
    count(DISTINCT CASE WHEN acp.circulate AND acl.circulate THEN acp.id ELSE NULL END) as circulatable,
    -- Circs could be on deleted copies, but we aren't including those here
    count(DISTINCT CASE WHEN (ac.stop_fines IS NULL OR ac.stop_fines IN ('MAXFINES')) AND ac.xact_finish IS NULL AND ac.checkin_time IS NULL THEN ac.id ELSE NULL END) AS open_circs,
    count(DISTINCT CASE WHEN ac.xact_start > NOW() - '6 months'::INTERVAL THEN ac.id ELSE NULL END)
        + count(DISTINCT CASE WHEN aac.xact_start > NOW() - '6 months'::INTERVAL THEN aac.id ELSE NULL END) AS aged_circs,
    count(DISTINCT ac.id) + count(DISTINCT aac.id) AS all_circs,
    -- For actual attempts at some visibility speedups. Note the sub-selects are for making NULLs go away, array_agg doesn't toss them out.
    COALESCE((SELECT ARRAY_AGG(x) FROM UNNEST(ARRAY_AGG(DISTINCT acp.circ_lib)) AS x WHERE x IS NOT NULL), ARRAY[]::INT[]) AS all_copy_libs,
    COALESCE((SELECT ARRAY_AGG(x) FROM UNNEST(ARRAY_AGG(DISTINCT CASE WHEN acp.opac_visible AND ccs.opac_visible AND acl.opac_visible AND aou.opac_visible THEN acp.circ_lib ELSE NULL END)) AS x WHERE x IS NOT NULL), ARRAY[]::INT[]) AS opac_copy_libs
FROM asset.copy acp
    JOIN asset.call_number acn ON acp.call_number = acn.id
    JOIN config.copy_status ccs ON acp.status = ccs.id
    JOIN asset.copy_location acl ON acp.location = acl.id
    JOIN actor.org_unit aou ON acp.circ_lib = aou.id
    JOIN pg_class p ON acp.tableoid = p.oid
    -- Join against action.circulation and action.aged_circulation instead of using the view for speed reasons
    LEFT JOIN action.circulation ac ON acp.id = ac.target_copy
    LEFT JOIN action.aged_circulation aac ON acp.id = aac.target_copy
    RIGHT JOIN biblio.record_entry bre ON acn.record = bre.id
WHERE
    (acp IS NULL OR NOT acp.deleted)
GROUP BY bre.id