Shoppable Room SetsCompany: Wayfair | Difficulty: medium

Wayfair's merchandising team creates curated room sets with required product categories. A room set is shoppable only when at least one active, in-stock catalog item exists for every category required by that room.

Write a query to find all fully shoppable room sets. Return one row per qualifying room with the columns in this exact order: room_id, room_name, required_categories, and available_categories.

Count each category only once, even if multiple products satisfy it. Exclude catalog items that are inactive, have zero stock_units, or have a NULL stock_units value. Sort the results by room_id in descending order.

Table Schema

Table	Column	Type
`room_requirements`	`room_id`	integer
`room_requirements`	`room_name`	varchar(60)
`room_requirements`	`required_category`	varchar(40)
`catalog_inventory`	`product_id`	integer
`catalog_inventory`	`category`	varchar(40)
`catalog_inventory`	`stock_units`	integer
`catalog_inventory`	`is_active`	boolean

Example Input: `room_requirements`

room_id	room_name	required_category
1	Coastal Bedroom	`bed`
1	Coastal Bedroom	`nightstand`
1	Coastal Bedroom	`lamp`
2	Home Office Nook	`desk`
2	Home Office Nook	`office_chair`
2	Home Office Nook	`monitor_arm`

Example Input: `catalog_inventory`

product_id	category	stock_units	is_active
101	`bed`	4	`TRUE`
102	`nightstand`	2	`TRUE`
103	`lamp`	0	`TRUE`
104	`desk`	7	`TRUE`
105	`office_chair`	3	`TRUE`
106	`monitor_arm`	5	`TRUE`

Example Output Explanation

Coastal Bedroom requires bed, nightstand, and lamp. bed and nightstand each have active, in-stock items, but the only lamp product has stock_units = 0, so lamp has zero available items anywhere in the catalog. Since one required category is unavailable, Coastal Bedroom is excluded. Home Office Nook requires desk, office_chair, and monitor_arm, and all three have active, in-stock items, so it is fully shoppable and appears in the output.

SchemaDROP TABLE IF EXISTS catalog_inventory; DROP TABLE IF EXISTS room_requirements; CREATE TABLE room_requirements ( room_id INT, room_name VARCHAR(60), required_category VARCHAR(40) ); CREATE TABLE catalog_inventory ( product_id INT, category VARCHAR(40), stock_units INT, is_active BOOLEAN ); INSERT INTO room_requirements VALUES (1,'Coastal Bedroom','bed'), (1,'Coastal Bedroom','nightstand'), (1,'Coastal Bedroom','lamp'), (2,'Home Office Nook','desk'), (2,'Home Office Nook','office_chair'), (2,'Home Office Nook','monitor_arm'); INSERT INTO catalog_inventory VALUES (101,'bed',4,TRUE), (102,'nightstand',2,TRUE), (103,'lamp',0,TRUE), (104,'desk',7,TRUE), (105,'office_chair',3,TRUE), (106,'monitor_arm',5,TRUE);← All SQL Challenges · Wayfair Questions · Learn SQL Free