Query to count "lost" copies by the profile group of the user that "lost" them.

example_output.txt

                 profile                  | lost_count
------------------------------------------+------------
 Users -> Patrons                         |      13790
 Users -> Patrons -> Interloan            |          3
 Users -> Patrons -> Limited              |        276
 Users -> Patrons -> Special-No-Fines     |          3
 Users -> Staff -> Catalogers             |          2
(5 rows)

lost_count_by_profile_group.sql

-- Count copies in "Lost" or "Lost and Paid" status that have an open circulation with
-- stop_fines of LOST, grouped by the primary profile of that circulation's user

-- See comments below for additional limiters

-- Neat CTE to give full path of permission groups
-- modified from an example by Dan Scott
WITH RECURSIVE profile_tree(id, parent, name) AS (
    SELECT id, parent, name FROM permission.grp_tree pgt WHERE id = 1
UNION ALL
    SELECT pgt.id, pgt.parent, profile_tree.name || ' -> ' || pgt.name
    FROM permission.grp_tree pgt
    INNER JOIN profile_tree ON pgt.parent = profile_tree.id
    WHERE pgt.parent IS NOT NULL
)
SELECT profile_tree.name AS profile, COUNT(*) AS lost_count
FROM asset.copy acp
JOIN config.copy_status ccs ON acp.status = ccs.id
-- rely on action.open_circulation view rather than trying to use
-- something like "most recent circulation"
JOIN action.open_circulation ocirc ON ocirc.target_copy = acp.id
JOIN actor.usr au ON ocirc.usr = au.id
JOIN profile_tree ON au.profile = profile_tree.id
WHERE acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(1)) -- option to limit by copy circ lib
AND acp.status IN (3, 17) -- "Lost", "Lost and Paid"
AND ocirc.stop_fines = 'LOST'
-- uncomment to exclude deleted copies
--AND NOT acp.deleted
-- uncomment to exclude where transaction is closed (usually because of zero balance)
--AND ocirc.xact_finish IS NULL
GROUP BY profile_tree.name
ORDER BY profile_tree.name;

survey_state_of_lost_data.sql

-- Get an idea of how well "Lost" and "Lost and Paid" copy status match up
-- with "LOST" stop_fines in your database
-- Databases with migrations in their history may differ greatly
SELECT COALESCE(ocirc.stop_fines, '[no open circ]') AS stop_fines, ccs.name AS status, count(*)
FROM asset.copy acp
JOIN config.copy_status ccs ON acp.status = ccs.id
LEFT JOIN action.open_circulation ocirc ON ocirc.target_copy = acp.id
WHERE acp.status IN (3, 17) -- "Lost", "Lost and Paid"
OR ocirc.stop_fines = 'LOST'
GROUP BY ocirc.stop_fines, ccs.name
ORDER BY ocirc.stop_fines, ccs.name;

survey_state_of_lost_data_output.txt

   stop_fines   |          status          | count
----------------+--------------------------+-------
 CLAIMSRETURNED | Lost                     |     5
 LOST           | Checked out              |   280
 LOST           | Damaged                  |     2
 LOST           | Lost                     | 27079
 [no open circ] | Lost                     |     1
(5 rows)