{x}
blog image

Rearrange Products Table

Table: Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
product_id is the primary key (column with unique values) for this table.
Each row in this table indicates the product's price in 3 different stores: store1, store2, and store3.
If the product is not available in a store, the price will be null in that store's column.

 

Write a solution to rearrange the Products table so that each row has (product_id, store, price). If a product is not available in a store, do not include a row with that product_id and store combination in the result table.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
Output: 
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
Explanation: 
Product 0 is available in all three stores with prices 95, 100, and 105 respectively.
Product 1 is available in store1 with price 70 and store3 with price 80. The product is not available in store2.

Solution Explanation: Rearrange Products Table

The problem asks to restructure the Products table, which stores product prices across three stores, into a format where each row represents a single product's price at a specific store. The solution uses SQL's UNION operator to achieve this efficiently.

Approach: Using UNION

The core idea is to generate three separate result sets, one for each store, and then combine them using UNION. Each individual query selects product_id and the price from a specific store column, along with a string literal to identify the store. The WHERE clause filters out rows where the price is NULL, ensuring that only available products are included. Finally, the UNION operator combines these sets, eliminating duplicate rows.

Time Complexity: O(n), where n is the number of rows in the Products table. This is because each SELECT statement processes the table once, and UNION has a linear time complexity in the number of rows being combined.

Space Complexity: O(n) in the worst case, as the result set can contain up to 3n rows (if every product is available in all three stores). It could be less than 3n if some products are missing from some stores.

MySQL Code and Explanation

SELECT product_id, 'store1' AS store, store1 AS price FROM Products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' AS store, store2 AS price FROM Products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' AS store, store3 AS price FROM Products WHERE store3 IS NOT NULL;

Line-by-line breakdown:

  1. SELECT product_id, 'store1' AS store, store1 AS price FROM Products WHERE store1 IS NOT NULL: This selects the product_id, assigns the literal string 'store1' to the store column, selects the price from the store1 column, and filters to include only rows where store1 is not NULL.

  2. UNION: This combines the results of the first SELECT statement with the subsequent ones, removing duplicate rows.

  3. SELECT product_id, 'store2' AS store, store2 AS price FROM Products WHERE store2 IS NOT NULL: This does the same as the first query, but for store2.

  4. UNION: Another UNION operation to combine with the next query.

  5. SELECT product_id, 'store3' AS store, store3 AS price FROM Products WHERE store3 IS NOT NULL: This does the same as the first and second query, but for store3.

This approach is efficient and leverages the built-in capabilities of SQL for a concise and readable solution. Other database systems may offer similar functionalities for achieving the same outcome, potentially using slightly different syntax.