Rearrange Products Table - Practice Coding Problems

Rearrange Products Table - Problem

Database Easy

Data Transformation Challenge: Rearrange Products Table

You're working with an e-commerce database that tracks product prices across multiple stores. The current table structure stores all store prices in separate columns, but you need to transform it into a more flexible format for analytics.

The Challenge:
Given a Products table with columns product_id, store1, store2, and store3, transform it so that each row represents a single product-store-price combination.

Input Format:
A table where each row contains a product's prices across 3 stores. If a product isn't available in a store, the price is NULL.

Output Format:
A normalized table with columns product_id, store, and price. Skip any NULL prices - only include rows where the product is actually available in that store.

Example:
Input table with wide format → Output table with long format (one row per product-store combination)

Input & Output

example_1.sql — Basic Transformation

$ 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 |

💡 Note: Product 0 is available in all 3 stores, so we get 3 rows. Product 1 is only available in store1 and store3 (store2 is null), so we get 2 rows. Total: 5 rows in the result.

example_2.sql — All Stores Available

$ Input: Products table: | product_id | store1 | store2 | store3 | |------------|--------|--------|--------| | 0 | 10 | 20 | 30 | | 1 | 40 | 50 | 60 |

› Output: | product_id | store | price | |------------|-----------|-------| | 0 | store1 | 10 | | 0 | store2 | 20 | | 0 | store3 | 30 | | 1 | store1 | 40 | | 1 | store2 | 50 | | 1 | store3 | 60 |

💡 Note: Both products are available in all stores, so we get 2 × 3 = 6 rows in the result. Each product gets transformed into 3 rows, one for each store.

example_3.sql — Many NULL Values

$ Input: Products table: | product_id | store1 | store2 | store3 | |------------|--------|--------|--------| | 0 | null | null | 105 | | 1 | 70 | null | null | | 2 | null | 80 | null |

› Output: | product_id | store | price | |------------|-----------|-------| | 0 | store3 | 105 | | 1 | store1 | 70 | | 2 | store2 | 80 |

💡 Note: Each product is only available in one store: Product 0 only in store3, Product 1 only in store1, Product 2 only in store2. This results in exactly 3 rows - one per product.

Visualization

Tap to expand

Understanding the Visualization

Identify Source Structure

Wide table with product_id and 3 store price columns

Plan Target Structure

Long table with product_id, store, price columns

Apply UNION ALL

Combine 3 filtered queries - one per store column

Filter NULL Values

Each query only includes rows where that store's price exists

Key Takeaway

🎯 Key Insight: UNION ALL efficiently combines three targeted queries, each extracting one store's data while filtering out NULL values, transforming wide format to normalized long format in a single operation.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the Products table, scanning it three times but with early filtering

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of non-NULL price entries (typically much less than n×s)

✓ Linear Space

Constraints

1 ≤ Number of products ≤ 1000
0 ≤ price ≤ 1000 (when not NULL)
Prices can be NULL indicating product not available in that store
product_id is unique (primary key)
Result should exclude rows where price IS NULL

Asked in

a Amazon 45 G Google 30 f Meta 25 ⊞ Microsoft 20

The optimal solution uses UNION ALL to combine three separate SELECT queries, each filtering one store column for non-NULL values. This pivot transformation converts the wide table format into a normalized long format efficiently in O(n) time.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Cross Join with Case Statements)	O(n × s)	O(n × s)	Create all possible product-store combinations, then filter out NULLs
UNION ALL Approach (Optimal)	O(n)	O(k)	Use UNION ALL to combine three filtered queries in one pass

Brute Force (Cross Join with Case Statements) — Algorithm Steps

Create a temporary table or CTE with store names (store1, store2, store3)
Cross join Products with the stores table to create all combinations
Use CASE statements to select the appropriate price column for each store
Filter out rows where the price is NULL

Visualization

Tap to expand

Step-by-Step Walkthrough

Cross Join

Create all product × store combinations

CASE Statements

Map each store to its corresponding price column

Filter NULLs

Remove rows where price is NULL

Code -

solution.c — C

-- Basic SQL transformation (brute force approach)
SELECT product_id, 'store1' AS store, store1 AS price FROM Products WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id, 'store2' AS store, store2 AS price FROM Products WHERE store2 IS NOT NULL  
UNION ALL
SELECT product_id, 'store3' AS store, store3 AS price FROM Products WHERE store3 IS NOT NULL;

Time & Space Complexity

Time Complexity

⏱️

O(n × s)

Where n is number of products and s is number of stores. Creates n×s intermediate rows

✓ Linear Growth

Space Complexity

O(n × s)

Temporary table holds all product-store combinations before filtering

⚡ Linearithmic Space

Constraints

1 ≤ Number of products ≤ 1000
0 ≤ price ≤ 1000 (when not NULL)
Prices can be NULL indicating product not available in that store
product_id is unique (primary key)
Result should exclude rows where price IS NULL

42.0K Views

High Frequency

~15 min Avg. Time

1.9K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Cross Join with Case Statements) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler