Inventory Quantity by Type | Dataford Interview Questions

Guidewire PolicyCenter implementation teams need an inventory summary for configuration assets. Write a PostgreSQL query that reports the total quantity of active inventory records for every inventory type, including types with no active records.

Requirements

Join inventory_types to inventory_items using the inventory type identifier.
Include only items where status = 'active', while retaining inventory types with no matching active items.
Sum quantity by inventory type and return 0 when no active quantity exists. PostgreSQL SUM should naturally ignore a NULL quantity.
Sort the output alphabetically by inventory type name.

Schema

inventory_types

Column	Type	Description
inventory_type_idPK	INT	Unique inventory type identifier
type_name	VARCHAR(100)	Name of the Guidewire PolicyCenter inventory type
owner_team	VARCHAR(100)	Team responsible for the inventory type

inventory_items

Column	Type	Description
inventory_item_idPK	INT	Unique inventory item identifier
inventory_type_id	INT	Referenced inventory type identifier
quantity	INT	Quantity represented by the inventory item
status	VARCHAR(20)	Inventory item lifecycle status

Your question is Inventory Quantity by Type. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem