Analyzing Product Inventory with Array Aggregation

This challenge focuses on leveraging SQL's array aggregation functions to analyze product inventory data. You'll need to combine information from multiple rows into a single array, allowing for more complex queries and insights into product relationships and stock levels. This is a common task when you need to present related data concisely or perform operations on groups of items.

Problem Description

You are given two tables: Products and Inventory. The Products table contains information about individual products, and the Inventory table tracks the stock levels of these products in different warehouses. Your task is to write a SQL query that, for each product, returns a list of warehouses where it is stocked and the quantity available in each of those warehouses.

Key Requirements:

1. Group by Product: The output should be organized by product.
2. Array of Warehouse Information: For each product, you need to generate an array. Each element in this array should represent a single warehouse where the product is stocked and contain the warehouse identifier and the quantity of that product in that warehouse.
3. Data Structure for Array Elements: Each element within the generated array should be a structured object or tuple containing two pieces of information: the warehouse_id and the quantity.

Expected Behavior:

The query should produce a result set where each row represents a unique product. This row will contain the product's ID and a single column holding an array. This array will contain aggregated information about its inventory across all warehouses.

Edge Cases:

• Products with no inventory: If a product exists in the Products table but has no corresponding entries in the Inventory table, it should still appear in the result. The array for such a product should be empty.
• Products stocked in multiple warehouses: The array should correctly aggregate all warehouse entries for a given product.

Examples

Example 1:

Input Tables:

Products:
+---------+------------+
| product_id | product_name |
+---------+------------+
| 101     | Laptop     |
| 102     | Mouse      |
| 103     | Keyboard   |
+---------+------------+

Inventory:
+---------+------------+----------+
| inventory_id | product_id | warehouse_id | quantity |
+---------+------------+----------+
| 1       | 101        | W1       | 50       |
| 2       | 101        | W2       | 30       |
| 3       | 102        | W1       | 100      |
| 4       | 103        | W3       | 25       |
+---------+------------+----------+

Output:
+---------+------------+--------------------------------------+
| product_id | product_name | warehouse_inventory                  |
+---------+------------+--------------------------------------+
| 101     | Laptop     | [{"warehouse_id": "W1", "quantity": 50}, {"warehouse_id": "W2", "quantity": 30}] |
| 102     | Mouse      | [{"warehouse_id": "W1", "quantity": 100}]                              |
| 103     | Keyboard   | [{"warehouse_id": "W3", "quantity": 25}]                               |
+---------+------------+--------------------------------------+

Explanation:
For 'Laptop' (product_id 101), it's in 'W1' with 50 and 'W2' with 30, so the array contains two elements.
For 'Mouse' (product_id 102), it's only in 'W1' with 100, so the array has one element.
For 'Keyboard' (product_id 103), it's only in 'W3' with 25, so the array has one element.

Example 2:

Input Tables:

Products:
+---------+------------+
| product_id | product_name |
+---------+------------+
| 201     | Monitor    |
| 202     | Webcam     |
+---------+------------+

Inventory:
+---------+------------+----------+
| inventory_id | product_id | warehouse_id | quantity |
+---------+------------+----------+
| 5       | 201        | W2       | 15       |
| 6       | 201        | W4       | 10       |
+---------+------------+----------+

Output:
+---------+------------+--------------------------------------+
| product_id | product_name | warehouse_inventory                  |
+---------+------------+--------------------------------------+
| 201     | Monitor    | [{"warehouse_id": "W2", "quantity": 15}, {"warehouse_id": "W4", "quantity": 10}] |
| 202     | Webcam     | []                                   |
+---------+------------+--------------------------------------+

Explanation:
'Monitor' (product_id 201) is in 'W2' with 15 and 'W4' with 10.
'Webcam' (product_id 202) exists in the Products table but has no entries in Inventory, resulting in an empty array.

Constraints

• The Products table will contain at least 1 product and at most 1000 products.
• The Inventory table will contain at least 0 entries and at most 5000 entries.
• product_id will be an integer.
• product_name will be a string up to 50 characters.
• warehouse_id will be a string up to 10 characters.
• quantity will be an integer between 0 and 1000.
• The query should execute efficiently, aiming for a resolution time under 1 second on a moderately sized dataset (similar to the examples).

Notes

• You will need to use a LEFT JOIN to ensure that products without any inventory are included in the results.
• The specific syntax for array aggregation functions varies between SQL dialects (e.g., ARRAY_AGG in PostgreSQL, JSON_ARRAYAGG in MySQL, COLLECT in Oracle). Choose the syntax appropriate for your chosen SQL environment or use a more generic pseudocode representation that indicates the intent.
• When constructing the elements within the array, you'll need to combine warehouse_id and quantity into a single, structured item. This might involve creating a tuple, a JSON object, or a custom structure depending on the SQL dialect's capabilities.