-- First ten users created in the system
SELECT *
FROM actor.usr
ORDER BY create_date
LIMIT 10;

-- First name / last name of the 10th through 20th users
-- ordered by last name
-- whose home library is set to Branch 1
SELECT first_given_name, family_name
FROM actor.usr
WHERE home_ou = (
    SELECT id
    FROM actor.org_unit
    WHERE name = 'Example Branch 1'
)
ORDER BY family_name
LIMIT 10
OFFSET 10;

SELECT aou.name, COUNT(*) AS "Undeleted users"
FROM actor.org_unit aou
  INNER JOIN actor.usr au ON au.home_ou = aou.id
WHERE au.deleted IS FALSE
GROUP BY aou.name
ORDER BY "Undeleted users";

-- #4
SELECT email, usrname
FROM actor.usr
WHERE family_name IN ('Scott', 'Smith')
AND active IS TRUE;


SELECT email, usrname
FROM actor.usr
WHERE (family_name  = 'Scott' OR family_name = 'Smith')
AND active IS TRUE;

-- INNER JOIN practice
-- #1
SELECT usrname, au.email, aou.name
FROM actor.usr au
  INNER JOIN actor.org_unit aou ON aou.id = au.home_ou
ORDER BY family_name DESC
LIMIT 10;

-- #2
SELECT barcode
FROM actor.card ac
  INNER JOIN actor.usr au ON au.id = ac.usr
  INNER JOIN actor.org_unit aou ON aou.id = au.home_ou
WHERE aou.name ~ 'Branch 1'
LIMIT 10;

-- #3
SELECT au.usrname, COUNT(*) AS "Transactions"
FROM actor.usr au
  INNER JOIN actor.org_unit aou ON aou.id = au.home_Ou
  INNER JOIN action.circulation circ ON circ.usr = au.id
WHERE aou.name ~ 'Branch 1'
GROUP BY au.usrname
ORDER BY "Transactions" DESC
;