Sum Quantity by Inventory Type | Dataford Interview Questions

A Guidewire Cloud Data Platform support team maintains store inventory used for implementation and customer enablement activities. Write a PostgreSQL query that reports the total available quantity for every inventory type.

Requirements

Return every row from inventory_types, including types with no active items.
Sum quantity only for store_items whose status is active.
Treat missing or NULL quantities as zero and return zero when a type has no active inventory.
Sort the results alphabetically by inventory type name.

Schema

inventory_types

Column	Type	Description
inventory_type_idPK	INTEGER	Primary key for an inventory type
inventory_type_name	VARCHAR(80)	Display name of the inventory type

store_items

Column	Type	Description
item_idPK	INTEGER	Primary key for an inventory item
inventory_type_id	INTEGER	References inventory_types.inventory_type_id
item_name	VARCHAR(120)	Name of the stored item
quantity	INTEGER	Current quantity of the item
status	VARCHAR(20)	Current item state

Your question is Sum Quantity by Inventory 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