Problem

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

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.

Solution

Method 1 – Pivot Table (CASE WHEN or crosstab)

Intuition

We need to pivot the table so that each row is a product and each store is a column. This is a classic SQL pivot/crosstab problem.

Approach

  1. Use conditional aggregation (CASE WHEN) in SQL to pivot the table.
  2. In pandas, use the pivot method.

Code

1
2
3
4
5
6

SELECT product_id,
       MAX(CASE WHEN store = 'store1' THEN price END) AS store1,
       MAX(CASE WHEN store = 'store2' THEN price END) AS store2,
       MAX(CASE WHEN store = 'store3' THEN price END) AS store3
FROM Products
GROUP BY product_id;
1
2
3
4
5
6

SELECT product_id,
       MAX(CASE WHEN store = 'store1' THEN price END) AS store1,
       MAX(CASE WHEN store = 'store2' THEN price END) AS store2,
       MAX(CASE WHEN store = 'store3' THEN price END) AS store3
FROM Products
GROUP BY product_id;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Assume Products is a pandas DataFrame
import pandas as pd
def products_price_for_each_store(Products: pd.DataFrame) -> pd.DataFrame:
    result = Products.pivot(index='product_id', columns='store', values='price').reset_index()
    # Optional: ensure columns are in the right order
    for col in ['store1', 'store2', 'store3']:
        if col not in result.columns:
            result[col] = None
    result = result[['product_id', 'store1', 'store2', 'store3']]
    return result

Complexity

  • ⏰ Time complexity: O(N) where N is the number of rows in Products.
  • 🧺 Space complexity: O(P) where P is the number of products.