Product's Price for Each Store - Problem

Given a Products table that stores product prices for different stores, write a SQL query to find the price of each product in each store.

The Products table has the following structure:

  • product_id: Integer representing the product identifier
  • store: Enum with values ('store1', 'store2', 'store3') indicating which store sells the product
  • price: Integer representing the price of the product at that store

The combination of (product_id, store) is the primary key, meaning each product can have different prices at different stores.

Goal: Transform the data to show each product as a row with columns for each store's price. If a product is not available at a store, the price should be NULL.

Table Schema

Products
Column Name Type Description
product_id PK int Product identifier
store PK enum Store name ('store1', 'store2', 'store3')
price int Price of product at this store
Primary Key: (product_id, store)
Note: Each row represents a product available at a specific store with its price

Input & Output

Example 1 — Multiple Products and Stores
Input Table:
product_id store price
0 store1 95
0 store3 105
0 store2 70
1 store1 110
Output:
product_id store1 store2 store3
0 95 70 105
1 110
💡 Note:

Product 0 is available in all three stores with different prices (95, 70, 105). Product 1 is only available in store1 for 110, so store2 and store3 show NULL values.
Example 2 — Single Product Single Store
Input Table:
product_id store price
5 store2 200
Output:
product_id store1 store2 store3
5 200
💡 Note:

Product 5 is only available at store2 for 200. The other stores (store1 and store3) show NULL since this product is not sold there.

Constraints

  • 1 ≤ product_id ≤ 1000
  • 1 ≤ price ≤ 3000
  • store is one of 'store1', 'store2', or 'store3'
  • Each (product_id, store) combination appears at most once

Visualization

Tap to expand
Product's Price for Each Store INPUT: Products Table product_id store price 0 store1 95 0 store3 105 0 store2 100 1 store1 70 1 store3 80 Row-based format: Each row = one product in one store with price (Normalized data) ALGORITHM: PIVOT 1 GROUP BY product_id Aggregate rows per product 2 CASE for each store Create store columns 3 MAX() aggregation Extract single price value 4 NULL for missing No price = NULL SELECT product_id, MAX(CASE WHEN store='store1' THEN price END) AS store1, ... (store2, store3) FROM Products GROUP BY product_id; FINAL RESULT id store1 store2 store3 0 95 100 105 1 70 NULL 80 PIVOT Transformation Rows (vertical) --> Columns (horizontal) OK - Pivoted! One row per product Key Insight: PIVOT transforms row-based data into column-based format using CASE expressions inside aggregate functions. MAX/SUM extracts the single non-NULL value per group. NULL appears when a product isn't sold at a store. This is the classic row-to-column transformation pattern (unpivot is the reverse operation). TutorialsPoint - Product's Price for Each Store | Optimal Solution (PIVOT with CASE)

Related Problems

Asked in
Amazon 15 Microsoft 12 Google 8
23.4K Views
Medium Frequency
~8 min Avg. Time
892 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