Skip to main content
Talentd

Product's Price for Each Store - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min readAsked at: Amazon, McAfee
Practice this problem

Problem Statement

Table: Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store       | enum    |
| price       | int     |
+-------------+---------+
In SQL, (product_id, store) is the primary key for this table.
store is a category of type ('store1', 'store2', 'store3') where each represents the store this product is available at.
price is the price of the product at this store.

 

Find the price of each product in each store.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Products table:
+-------------+--------+-------+
| product_id  | store  | price |
+-------------+--------+-------+
| 0           | store1 | 95    |
| 0           | store3 | 105   |
| 0           | store2 | 100   |
| 1           | store1 | 70    |
| 1           | store3 | 80    |
+-------------+--------+-------+
Output: 
+-------------+--------+--------+--------+
| product_id  | store1 | store2 | store3 |
+-------------+--------+--------+--------+
| 0           | 95     | 100    | 105    |
| 1           | 70     | null   | 80     |
+-------------+--------+--------+--------+
Explanation: 
Product 0 price's are 95 for store1, 100 for store2 and, 105 for store3.
Product 1 price's are 70 for store1, 80 for store3 and, it's not sold in store2.

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
UNION ALL UnpivotO(n)O(1)Best general solution. Clear, readable, and supported in all SQL engines.
CROSS JOIN + CASE MappingO(n)O(1)Useful when dynamically mapping column labels to rows without repeating multiple SELECT queries.

Video Solution

LeetCode 1777: Product's Price for Each Store [SQL] • Frederik Müller • 2,746 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Product's Price for Each Store easy or hard?
Product's Price for Each Store is considered an easy SQL problem. The main challenge is recognizing that the store columns must be converted into rows using an unpivot-style query such as UNION ALL.
Product's Price for Each Store Python/Java solution
This problem is solved directly using SQL rather than Python or Java because it focuses on database querying. On platforms that support multiple languages, the SQL query performs the transformation using UNION ALL and conditional filtering.
How to solve Product's Price for Each Store in O(n)?
Use three SELECT queries combined with UNION ALL. Each query selects product_id, a constant store name, and the corresponding price column while filtering out NULL values. Because each row is scanned a fixed number of times, the overall complexity remains O(n).
What is the best approach for Product's Price for Each Store?
The most common approach uses SQL UNION ALL to unpivot store columns into rows. Each SELECT extracts one store's price and labels it with the store name, while filtering out NULL values. This keeps the query simple and runs in O(n) time where n is the number of product rows.
Is Product's Price for Each Store asked at Google/Amazon/Meta?
SQL data transformation problems like this commonly appear in database interview rounds at companies such as Amazon, Google, and Meta. The focus is on reshaping relational data and handling NULL values correctly using SQL constructs.
What data structure is used in Product's Price for Each Store?
The problem relies on relational database tables and SQL query operations. The main concept is transforming a wide table structure into a normalized row format using UNION ALL or similar unpivot techniques.
What is the time complexity of Product's Price for Each Store?
The time complexity is O(n) because each product row is processed a constant number of times (once per store column). Since the number of stores is fixed, the query scales linearly with the number of rows in the table.

Ready to solve this problem?

Practice Product's Price for Each Store with our built-in code editor and test cases.

Practice on FleetCode
Previous

Dynamic Pivoting of a Table

Next

Rearrange Products Table