SQL for Equipment Accountability

MediumSQL · PostgreSQL

Problem

Write a SQL query to track equipment accountability by comparing government-furnished equipment checkouts, returns, and missing items across multiple exercise dates.

Use the supplied exercise, equipment, and transaction data. Treat an item as missing when it was checked out for an exercise but has not been returned for that exercise.

Output

One row per exercise date, including dates with no transactions.
Columns: exercise_date, exercise_name, checked_out_count, returned_count, missing_count, and missing_equipment_codes.
Show missing equipment codes as a comma-separated, alphabetically ordered string, or an empty string when none are missing.
Order by exercise_date ascending.

Schema

exercises

Column	Type	Description
exercise_idPK	INT	Unique exercise identifier
exercise_name	VARCHAR(100)	Exercise name
exercise_date	DATE	Date of the exercise
notes	TEXT	Optional exercise notes

equipment

Column	Type	Description
equipment_idPK	INT	Unique equipment identifier
equipment_code	VARCHAR(30)	Readable equipment accountability code
equipment_type	VARCHAR(50)	Equipment category
serial_number	VARCHAR(50)	Manufacturer serial number

equipment_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
exercise_id	INT	Exercise associated with the transaction
equipment_id	INT	Equipment involved in the transaction
event_type	VARCHAR(20)	Transaction type, such as CHECKOUT or RETURN
event_date	DATE	Date of the transaction
returned_by	VARCHAR(100)	Person recording a return

Tablesexercisesequipmentequipment_transactions