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.
DROP 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);