LeetCode 1777 - Product's Price for Each Store

The problem provides a table named Products, where each row represents the price of a specific product in a specific store.

leetcodeeasydatabase

LeetCode Problem 1777

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

The problem provides a table named Products, where each row represents the price of a specific product in a specific store. Every row contains three values:

  • product_id, identifying the product
  • store, identifying which store sells the product
  • price, representing the price of that product in that store

The (product_id, store) pair is guaranteed to be unique because it is the primary key. This means a product cannot appear more than once for the same store.

The task is to transform the table from a row-oriented format into a column-oriented format. Instead of having one row per (product, store) pair, we want one row per product, with separate columns for each store:

  • store1
  • store2
  • store3

Each column should contain the corresponding price if the product exists in that store. If the product is not sold in a particular store, the value should be NULL.

This is essentially a pivoting problem. We are converting rows into columns based on the store name.

For example, if the input contains:

product_id store price
0 store1 95
0 store2 100
0 store3 105

then the output row becomes:

product_id store1 store2 store3
0 95 100 105

The problem guarantees that stores only come from the fixed set:

  • store1
  • store2
  • store3

This is important because we already know the exact columns that must appear in the output.

An important edge case is when a product does not exist in one or more stores. In that case, the output must contain NULL for those columns. Another important detail is that products may appear in only one store, so the query must still generate a complete row with missing values represented correctly.

Approaches

Brute Force Approach

A brute-force approach would manually query the table multiple times for each product. For every distinct product_id, we could search separately for the price in store1, store2, and store3.

Conceptually, this works like:

  1. Get all distinct product IDs.
  2. For each product:
  • Search for the row where store = store1
  • Search for the row where store = store2
  • Search for the row where store = store3
  1. Construct the final row.

This produces the correct answer because every product-store pair is checked independently. However, it is inefficient because the table may be scanned repeatedly for every product.

Optimal Approach

The better solution uses SQL aggregation with conditional logic.

The key insight is that each product should become exactly one row in the output. Therefore, we can group rows by product_id. Then, for each store column, we selectively extract the corresponding price using conditional aggregation.

We use expressions like:

MAX(CASE WHEN store = 'store1' THEN price END)

This works because:

  • The CASE expression returns the price only for rows matching the target store.
  • All other rows return NULL.
  • Since each (product_id, store) pair is unique, there is at most one non-null value per group.
  • MAX() simply extracts that value.

This converts multiple rows into a single row cleanly and efficiently.

Approach Time Complexity Space Complexity Notes
Brute Force O(n × s) O(1) Repeatedly scans data for each store
Optimal O(n) O(1) Uses grouping and conditional aggregation

Here, n is the number of rows and s is the number of stores, which is fixed at 3.

Algorithm Walkthrough

  1. Start by grouping all rows using product_id.

Grouping ensures that every product produces exactly one row in the final result. All store entries for the same product are collected together. 2. For the store1 column, evaluate a conditional expression.

Use:

CASE WHEN store = 'store1' THEN price END

This returns:

  • the price if the row belongs to store1
  • NULL otherwise
  1. Apply an aggregate function such as MAX().

Since each product has at most one row for each store, there will be at most one non-null value. MAX() extracts that value safely. 4. Repeat the same logic for store2 and store3.

Each store gets its own conditional aggregation column. 5. Return the grouped result.

The final output contains:

  • one row per product
  • one column per store
  • NULL for missing stores

Why it works

The correctness comes from the uniqueness of (product_id, store). For a given product and store combination, there is at most one price. The CASE expression isolates the desired store value, and the aggregate function extracts it from the group. Because grouping is performed by product_id, all store prices for the same product are merged into one output row.

Python Solution

Although this is a database problem and normally solved in SQL, the equivalent transformation logic can be demonstrated in Python.

from collections import defaultdict
from typing import List, Dict, Optional

class Solution:
    def productsPrice(self, products: List[List]) -> List[Dict[str, Optional[int]]]:
        result = defaultdict(lambda: {
            "store1": None,
            "store2": None,
            "store3": None
        })

        for product_id, store, price in products:
            result[product_id][store] = price

        answer = []

        for product_id, stores in result.items():
            row = {
                "product_id": product_id,
                "store1": stores["store1"],
                "store2": stores["store2"],
                "store3": stores["store3"]
            }
            answer.append(row)

        return answer

The implementation uses a dictionary keyed by product_id. Each product stores another dictionary containing prices for all three stores.

Initially, every store value is set to None. As rows are processed, the appropriate store price is updated.

Finally, the result is converted into the required output format, where every product has exactly one row and missing stores remain None.

For the actual LeetCode database submission, the intended SQL solution is:

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;

Go Solution

package main

import "fmt"

type Product struct {
	ProductID int
	Store     string
	Price     int
}

type Result struct {
	ProductID int
	Store1    *int
	Store2    *int
	Store3    *int
}

func productsPrice(products []Product) []Result {
	resultMap := make(map[int]*Result)

	for _, product := range products {
		if _, exists := resultMap[product.ProductID]; !exists {
			resultMap[product.ProductID] = &Result{
				ProductID: product.ProductID,
			}
		}

		current := resultMap[product.ProductID]

		switch product.Store {
		case "store1":
			price := product.Price
			current.Store1 = &price
		case "store2":
			price := product.Price
			current.Store2 = &price
		case "store3":
			price := product.Price
			current.Store3 = &price
		}
	}

	answer := []Result{}

	for _, value := range resultMap {
		answer = append(answer, *value)
	}

	return answer
}

func main() {
	products := []Product{
		{0, "store1", 95},
		{0, "store2", 100},
		{0, "store3", 105},
		{1, "store1", 70},
		{1, "store3", 80},
	}

	result := productsPrice(products)

	fmt.Println(result)
}

The Go implementation uses pointers for store prices so that missing values can be represented as nil, which corresponds to SQL NULL.

Unlike Python dictionaries, Go requires explicit struct definitions. A map[int]*Result is used to group products by ProductID.

The overall logic remains identical to the Python version.

For the actual LeetCode database problem, the SQL solution is still the expected answer.

Worked Examples

Example 1

Input:

product_id store price
0 store1 95
0 store3 105
0 store2 100
1 store1 70
1 store3 80

Step-by-step processing

Initially:

product_id store1 store2 store3
none none none none

Process row (0, store1, 95):

product_id store1 store2 store3
0 95 null null

Process row (0, store3, 105):

product_id store1 store2 store3
0 95 null 105

Process row (0, store2, 100):

product_id store1 store2 store3
0 95 100 105

Process row (1, store1, 70):

product_id store1 store2 store3
0 95 100 105
1 70 null null

Process row (1, store3, 80):

product_id store1 store2 store3
0 95 100 105
1 70 null 80

Final output:

product_id store1 store2 store3
0 95 100 105
1 70 null 80

Complexity Analysis

Measure Complexity Explanation
Time O(n) Each row is processed exactly once
Space O(p) Stores grouped information for each product

Here, n is the number of rows and p is the number of distinct products.

The SQL aggregation approach is linear with respect to the number of rows because each row participates in exactly one grouping operation and a constant number of conditional checks.

Test Cases

from collections import defaultdict

def products_price(products):
    result = defaultdict(lambda: {
        "store1": None,
        "store2": None,
        "store3": None
    })

    for product_id, store, price in products:
        result[product_id][store] = price

    answer = []

    for product_id, stores in result.items():
        answer.append({
            "product_id": product_id,
            "store1": stores["store1"],
            "store2": stores["store2"],
            "store3": stores["store3"]
        })

    return sorted(answer, key=lambda x: x["product_id"])

# Example case from problem statement
assert products_price([
    [0, "store1", 95],
    [0, "store3", 105],
    [0, "store2", 100],
    [1, "store1", 70],
    [1, "store3", 80]
]) == [
    {"product_id": 0, "store1": 95, "store2": 100, "store3": 105},
    {"product_id": 1, "store1": 70, "store2": None, "store3": 80}
]

# Product exists in only one store
assert products_price([
    [2, "store2", 50]
]) == [
    {"product_id": 2, "store1": None, "store2": 50, "store3": None}
]

# Multiple products with sparse stores
assert products_price([
    [1, "store1", 10],
    [2, "store2", 20],
    [3, "store3", 30]
]) == [
    {"product_id": 1, "store1": 10, "store2": None, "store3": None},
    {"product_id": 2, "store1": None, "store2": 20, "store3": None},
    {"product_id": 3, "store1": None, "store2": None, "store3": 30}
]

# Product available in all stores
assert products_price([
    [5, "store1", 5],
    [5, "store2", 10],
    [5, "store3", 15]
]) == [
    {"product_id": 5, "store1": 5, "store2": 10, "store3": 15}
]

# Empty input
assert products_price([]) == []
Test Why
Example input Validates standard multi-store behavior
Single-store product Ensures missing stores become null
Sparse products Verifies independent grouping
Product in all stores Confirms full population of columns
Empty input Tests boundary condition

Edge Cases

One important edge case occurs when a product exists in only one store. A naive implementation might accidentally omit missing store columns entirely. The correct behavior is to include all store columns and use NULL for stores where the product is unavailable. The implementation handles this by initializing every product with all store keys set to None.

Another edge case is when multiple products have completely different store availability patterns. For example, one product may appear only in store1 while another appears only in store3. The grouping logic ensures that each product is processed independently, so values never leak between products.

An additional edge case is empty input. If the table contains no rows, the result should also be empty. The implementation naturally handles this because the grouping structure remains empty and no output rows are generated.

A subtle edge case involves uniqueness guarantees. The problem guarantees (product_id, store) is unique. Without this guarantee, aggregate functions like MAX() could hide duplicate rows unintentionally. Since the uniqueness constraint exists, conditional aggregation safely extracts exactly one value per store.