Skip to main content
Talentd

Rearrange Products Table - Solution & Explanation

EasyDatabase7 min readAsked at: Amazon, Meta, Google
Practice this problem

Problem Statement

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.

Approach Overview

Problem Overview: You are given a Products table where each row contains a product_id and multiple store columns (store1, store2, store3). The task is to transform this wide table into a normalized row format: each non-null store price becomes a separate row with columns product_id, store, and price. This is essentially an unpivot operation commonly seen in database transformations.

Approach 1: Iterative Row and Column Traversal (O(n*k) time, O(1) extra space)

Treat the table like a small matrix: iterate through each product row, then check each store column individually. For every store field (store1, store2, store3), verify whether the value is non-null. When a price exists, emit a new row containing the product_id, the store name, and the price. The key insight is that the number of store columns is fixed and small, so a simple nested traversal works efficiently. This approach maps naturally to languages like Python or C# where you loop through rows and manually construct the output records. Time complexity is O(n*k), where n is the number of products and k is the number of store columns. Extra space is O(1) beyond the output since you only track the current row and column being processed. The pattern resembles iterating over a matrix with conditional filtering.

Approach 2: SQL-like Transformation with Language Features (O(n*k) time, O(k) space)

This approach models the solution after SQL UNPIVOT behavior using built-in language features. Create a small structure (array, map, or list) that pairs store names with their corresponding column values for each row. For example, convert a row into something like [("store1", value1), ("store2", value2), ("store3", value3)]. Iterate through this structure and append an output record whenever the value is not null. Languages like JavaScript or C make this clean by storing the store names in an array and iterating through them dynamically. The main insight is separating column metadata (store names) from row data, which simplifies the transformation logic. Time complexity remains O(n*k) since every product checks every store column. Space complexity is O(k) for the temporary store mapping used during each iteration. This style mirrors transformations commonly used in array-driven data reshaping pipelines.

Recommended for interviews: The iterative row and column traversal approach is typically expected. It clearly shows you understand how to normalize wide data structures by scanning each column and filtering null values. Demonstrating the SQL-style transformation afterward shows deeper familiarity with data reshaping patterns and how unpivot operations are implemented outside SQL engines.

Approach 1: Approach 1: Iterative Row and Column Traversal

This approach involves iterating over each row of the original table and for each product, iterating over the store columns. For each store, if the price is not null, a new entry with the product_id, store name, and price is added to the result.

This solution defines a function rearrange_products that takes a list of dictionaries as input, representing rows from the Products table. It iterates through each product and nested within that loop, iterates over the stores to check if the price is available. If so, the function appends a dictionary with the product_id, store, and price to the result list.

Code

PythonC#

Python

C#

Complexity

Time Complexity: O(n * m), where n is the number of products and m is the number of stores. Space Complexity: O(n * m), as we store results for each product-store where a price exists.

Try this approach in the editor →

Approach 2: Approach 2: SQL-like Transformation with Language Features

Using language constructs similar to SQL's UNPIVOT operation, we will extract non-null values for each store and create a new list with structure (product_id, store, price).

This JavaScript solution leverages functions like forEach to iterate over products and stores, adding new objects to result when a store has a non-null price.

Code

JavaScriptC

JavaScript

C

Complexity

Time Complexity: O(n * m), Space Complexity: O(n * m).

Try this approach in the editor →

Approach 3: Union

We can select the products and prices for each store, and then use the UNION operator to combine the results.

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Approach 1: Iterative Row and Column Traversal

Time Complexity: O(n * m), where n is the number of products and m is the number of stores. Space Complexity: O(n * m), as we store results for each product-store where a price exists.

Approach 2: SQL-like Transformation with Language Features

Time Complexity: O(n * m), Space Complexity: O(n * m).

Union—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Iterative Row and Column TraversalO(n*k)O(1)Best when the number of store columns is small and fixed; simple and interview-friendly.
SQL-like Transformation with Language FeaturesO(n*k)O(k)Useful when implementing generic column-to-row transformations or mimicking SQL UNPIVOT behavior.

Video Solution

LeetCode 1795 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 8,123 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Rearrange Products Table easy or hard?
Rearrange Products Table is categorized as an Easy problem with an acceptance rate above 85%. The logic is straightforward once you recognize that the task is an unpivot transformation that converts column-based store prices into individual rows.
Rearrange Products Table Python/Java solution
In Python or Java-style solutions, iterate through each row and manually check store1, store2, and store3. For each non-null value, append a record with product_id, store name, and price. This direct traversal approach keeps the implementation simple and runs in O(n*k) time.
How to solve Rearrange Products Table in O(n)?
Treat the number of store columns as a constant. Iterate through every product row once and check each store column directly. Because there are only three store columns, the complexity becomes O(n*3), which simplifies to O(n) in practice.
What is the best approach for Rearrange Products Table?
The most practical approach is iterative row and column traversal. Iterate through each product row and check each store column (store1, store2, store3). Whenever the value is not null, output a row containing product_id, store name, and price. This runs in O(n*k) time where n is the number of rows and k is the number of store columns.
Is Rearrange Products Table asked at Google/Amazon/Meta?
Problems involving table normalization and unpivot operations appear frequently in database interviews at companies like Amazon and Meta. They test your understanding of relational data transformations, especially converting wide tables into row-based formats.
What data structure is used in Rearrange Products Table?
The solution primarily works with tabular data structures similar to arrays of records. During processing, many implementations temporarily store store-name/value pairs in arrays or small maps to simulate a SQL UNPIVOT transformation.
What is the time complexity of Rearrange Products Table?
The time complexity is O(n*k). Each of the n product rows must check k store columns to determine whether a price exists. Since k is small and fixed in the problem, the solution effectively scales linearly with the number of rows.

Ready to solve this problem?

Practice Rearrange Products Table with our built-in code editor and test cases.

Practice on FleetCode
Previous

Dynamic Unpivoting of a Table

Next

Product's Price for Each Store