Product's Price for Each Store - Practice Coding Problems

Product's Price for Each Store - Problem

Database Easy

🏪 Product Price Comparison Across Stores

Imagine you're building a price comparison feature for an e-commerce platform! You have a database table containing product prices across different stores, but the data is stored in a normalized format (each row represents one product-store combination).

Your task: Transform this data into a user-friendly format where each product shows its price in all stores side by side, making it easy for customers to compare prices at a glance.

📊 Database Schema

Goal: Create a result table where each row represents a product, and columns show the price in each store (store1, store2, store3). If a product isn't available in a store, show null.

This is a classic pivot table operation - converting rows to columns for better data presentation!

Input & Output

example_1.sql — Basic Pivot Operation

$ Input: Products table: +------------+--------+-------+ | product_id | store | price | +------------+--------+-------+ | 0 | store1 | 95 | | 0 | store3 | 105 | | 0 | store2 | 100 | | 1 | store1 | 70 | | 1 | store2 | 80 | +------------+--------+-------+

› Output: +------------+--------+--------+--------+ | product_id | store1 | store2 | store3 | +------------+--------+--------+--------+ | 0 | 95 | 100 | 105 | | 1 | 70 | 80 | null | +------------+--------+--------+--------+

💡 Note: Product 0 is available in all three stores with prices 95, 100, and 105 respectively. Product 1 is only available in store1 (70) and store2 (80), so store3 shows null.

example_2.sql — Single Product Multiple Stores

$ Input: Products table: +------------+--------+-------+ | product_id | store | price | +------------+--------+-------+ | 5 | store1 | 200 | | 5 | store2 | 180 | | 5 | store3 | 220 | +------------+--------+-------+

› Output: +------------+--------+--------+--------+ | product_id | store1 | store2 | store3 | +------------+--------+--------+--------+ | 5 | 200 | 180 | 220 | +------------+--------+--------+--------+

💡 Note: Product 5 is available in all three stores, showing how a single product's prices get pivoted into one row with separate columns for each store.

example_3.sql — Edge Case: Products with Limited Availability

$ Input: Products table: +------------+--------+-------+ | product_id | store | price | +------------+--------+-------+ | 10 | store2 | 50 | | 11 | store3 | 75 | | 12 | store1 | 25 | +------------+--------+-------+

› Output: +------------+--------+--------+--------+ | product_id | store1 | store2 | store3 | +------------+--------+--------+--------+ | 10 | null | 50 | null | | 11 | null | null | 75 | | 12 | 25 | null | null | +------------+--------+--------+--------+

💡 Note: Each product is available in only one store, demonstrating how the pivot operation handles sparse data by filling unavailable combinations with null values.

Visualization

Tap to expand

Understanding the Visualization

Identify the Pattern

We have product-store-price combinations that need to become product rows with store columns

Group by Product

Collect all rows for each product ID together

Apply Conditional Logic

Use CASE statements to route each price to the correct store column

Aggregate the Results

Use MAX to extract the single price value for each store (or NULL if not available)

Generate Final Table

Output one row per product with separate columns for each store's price

Key Takeaway

🎯 Key Insight: Conditional aggregation allows us to transform rows into columns efficiently by using CASE statements within aggregate functions, all while maintaining optimal O(n) performance with a single table scan.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all rows in the products table

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique products (for GROUP BY operation)

✓ Linear Space

Constraints

1 ≤ number of rows ≤ 10⁴
store is an ENUM of type ('store1', 'store2', 'store3')
1 ≤ product_id ≤ 10⁶
1 ≤ price ≤ 10⁶
Primary key: (product_id, store) - no duplicate combinations

Asked in

a Amazon 45 G Google 35 ⊞ Microsoft 30 f Meta 25

The optimal solution uses conditional aggregation with CASE statements and GROUP BY. By using MAX(CASE WHEN store = 'store1' THEN price END) for each store, we can pivot the data in a single table scan, making it much more efficient than multiple subqueries. This is the standard SQL pattern for converting rows to columns.

Common Approaches

Approach	Time	Space	Notes
✓ Conditional Aggregation (Optimal)	O(n)	O(k)	Use CASE statements with GROUP BY to pivot data in a single pass
Subquery Approach (Brute Force)	O(n * m)	O(n * m)	Use separate subqueries for each store to find prices

Conditional Aggregation (Optimal) — Algorithm Steps

Group all rows by product_id
Use CASE statements within MAX/MIN aggregate functions
Each CASE statement checks the store value and returns price or NULL
Aggregate functions collect the non-NULL price for each store column
Return the final pivoted result

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Table Once

Read through all rows in the products table

Group by Product

Group rows by product_id

Apply CASE Logic

Each CASE statement routes prices to correct store column

Aggregate Results

MAX function picks the non-NULL price for each store

Output Final Table

Return pivoted result with one row per product

Code -

solution.c — C

-- Optimal SQL Query using Conditional Aggregation
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
ORDER BY product_id;

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all rows in the products table

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique products (for GROUP BY operation)

✓ Linear Space

Constraints

1 ≤ number of rows ≤ 10⁴
store is an ENUM of type ('store1', 'store2', 'store3')
1 ≤ product_id ≤ 10⁶
1 ≤ price ≤ 10⁶
Primary key: (product_id, store) - no duplicate combinations

32.0K Views

High Frequency

~15 min Avg. Time

1.5K 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

🏪 Product Price Comparison Across Stores

📊 Database Schema

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Conditional Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler