Product's Price for Each Store - Practice Coding Problems

Product's Price for Each Store - Problem

Database Easy

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

Understanding the Visualization

Input

Products table with separate rows per store

Pivot

GROUP BY + CASE WHEN to create columns

Output

One row per product with store price columns

Key Takeaway

🎯 Key Insight: Use conditional aggregation to pivot normalized data into a cross-tabular format

Asked in

A Amazon 15 M Microsoft 12 G Google 8

Use conditional aggregation with CASE WHEN statements and GROUP BY product_id to pivot store rows into columns, showing each product's price at each store.

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

Common Approaches

Approach	Time	Space	Notes
✓ Conditional Aggregation (Pivot)	O(n)	O(k)	Use CASE WHEN with GROUP BY to pivot store rows into columns

Conditional Aggregation (Pivot) — Algorithm Steps

Step 1: GROUP BY product_id to get one row per product
Step 2: Use CASE WHEN for each store to conditionally select prices
Step 3: Apply MAX() to handle the grouped values (only one non-NULL per group)

Visualization

Tap to expand

Step-by-Step Walkthrough

Group

Group by product_id

Pivot

CASE WHEN for each store

Aggregate

MAX() to get single values

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all rows with grouping

✓ Linear Growth

Space Complexity

O(k)

Where k is number of distinct products

✓ Linear Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Conditional Aggregation (Pivot) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler