Product's Price for Each Store - Video Solutions

Problem Overview: The table stores product prices in separate columns for each store (store1, store2, store3). The task is to convert this wide format into a normalized result where each row contains product_id, store, and price, excluding NULL prices.

Approach 1: UNION ALL Unpivot (O(n) time, O(1) space)

The straightforward solution is to simulate an unpivot operation using UNION ALL. Run three separate SELECT queries—one for each store column—and combine them with UNION ALL. Each query outputs the same schema: product_id, a constant store name, and the corresponding price column. Add a WHERE price IS NOT NULL filter in each query so rows with missing prices are excluded. Because each row from the table is scanned once per store column, the time complexity is O(n) relative to the number of products, and space complexity is O(1) aside from the result set. This approach is simple, readable, and widely supported across SQL engines.

Approach 2: CROSS JOIN with Conditional Selection (O(n) time, O(1) space)

Another method is to generate store labels using a small derived table and combine it with the products table using CROSS JOIN. The query then uses a CASE expression to map each store label (store1, store2, store3) to the correct column. After computing the value, filter out rows where the resulting price is NULL. This approach effectively converts columns into rows without repeating multiple SELECT blocks. Time complexity remains O(n) because each product is evaluated for a fixed number of stores, and space complexity stays O(1). It’s useful when the number of store columns is small and fixed.

Both solutions rely on standard database querying techniques and simple SQL transformations. The key idea is recognizing that the schema must be normalized by converting columns into rows. Using UNION ALL is the most explicit way to do this and is often easier to read during code reviews. The technique is conceptually similar to an UNION-based data transformation.

Recommended for interviews: The UNION ALL approach. Interviewers expect a clear unpivot strategy using multiple selects. It shows you understand how to reshape relational data and handle NULL filtering cleanly. The CROSS JOIN method demonstrates deeper SQL flexibility but is less common for quick interview solutions.