Dynamic Store Price Pivot Map

Hard

A retail intelligence team receives product pricing in long format, where each row represents one product-store quote. They need a dynamic pivot view that is stable for downstream systems even when the set of stores changes over time. Table: Products product_id: product identifier store: store name price: product price in that store Data guarantees: (product_id, store) is unique. There are at most 30 distinct stores in one input. Task: Build a canonical dynamic pivot string per product. Definitions: Let U be the set of all distinct stores in Products. Sort U lexicographically. For each product_id, generate store_price_map by traversing sorted U and emitting: store=price if that product has a row for that store store=NULL if it does not Join all tokens with semicolons. Output requirements: Return exactly these columns: product_id, store_price_map Sort by product_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Products:
| product_id | store    | price |
|------------|----------|-------|
| 1          | Shop     | 110   |
| 1          | LC_Store | 100   |
| 2          | Nozama   | 200   |
| 2          | Souq     | 190   |
| 3          | Shop     | 1000  |
| 3          | Souq     | 1900  |

Output

[
  {"product_id":1,"store_price_map":"LC_Store=100;Nozama=NULL;Shop=110;Souq=NULL"},
  {"product_id":2,"store_price_map":"LC_Store=NULL;Nozama=200;Shop=NULL;Souq=190"},
  {"product_id":3,"store_price_map":"LC_Store=NULL;Nozama=NULL;Shop=1000;Souq=1900"}
]

Explanation

Global store domain is {LC_Store, Nozama, Shop, Souq}. Every product must list all four stores in lexicographic order, filling missing prices with NULL.

Example 2

Input

Products:
| product_id | store | price |
|------------|-------|-------|
| 10         | A1    | 7     |
| 10         | A10   | 5     |
| 11         | A2    | 9     |

Output

[
  {"product_id":10,"store_price_map":"A1=7;A10=5;A2=NULL"},
  {"product_id":11,"store_price_map":"A1=NULL;A10=NULL;A2=9"}
]

Explanation

Lexicographic ordering is string-based, so A10 appears between A1 and A2.

Example 3

Input

Products:
[]

Output

[]

Explanation

No rows means no products and no store domain, so output is empty.

Constraints

0 <= number of rows in Products <= 1600
1 <= product_id <= 1000000000
1 <= len(store) <= 20
store contains only letters, digits, and underscore
0 <= price <= 1000000000
(product_id, store) is unique
Number of distinct stores per input <= 30
For each product, store_price_map must enumerate every global store in lexicographic order
Missing product-store combinations must be encoded as store=NULL
Return exactly columns: product_id, store_price_map
Sort final output by product_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly product_id and store_price_map

Loading problem...

Preparing your editor. You can read the problem while it loads.

Dynamic Store Price Pivot Map

Hard

A retail intelligence team receives product pricing in long format, where each row represents one product-store quote. They need a dynamic pivot view that is stable for downstream systems even when the set of stores changes over time. Table: Products product_id: product identifier store: store name price: product price in that store Data guarantees: (product_id, store) is unique. There are at most 30 distinct stores in one input. Task: Build a canonical dynamic pivot string per product. Definitions: Let U be the set of all distinct stores in Products. Sort U lexicographically. For each product_id, generate store_price_map by traversing sorted U and emitting: store=price if that product has a row for that store store=NULL if it does not Join all tokens with semicolons. Output requirements: Return exactly these columns: product_id, store_price_map Sort by product_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Products:
| product_id | store    | price |
|------------|----------|-------|
| 1          | Shop     | 110   |
| 1          | LC_Store | 100   |
| 2          | Nozama   | 200   |
| 2          | Souq     | 190   |
| 3          | Shop     | 1000  |
| 3          | Souq     | 1900  |

Output

[
  {"product_id":1,"store_price_map":"LC_Store=100;Nozama=NULL;Shop=110;Souq=NULL"},
  {"product_id":2,"store_price_map":"LC_Store=NULL;Nozama=200;Shop=NULL;Souq=190"},
  {"product_id":3,"store_price_map":"LC_Store=NULL;Nozama=NULL;Shop=1000;Souq=1900"}
]

Explanation

Global store domain is {LC_Store, Nozama, Shop, Souq}. Every product must list all four stores in lexicographic order, filling missing prices with NULL.

Example 2

Input

Products:
| product_id | store | price |
|------------|-------|-------|
| 10         | A1    | 7     |
| 10         | A10   | 5     |
| 11         | A2    | 9     |

Output

[
  {"product_id":10,"store_price_map":"A1=7;A10=5;A2=NULL"},
  {"product_id":11,"store_price_map":"A1=NULL;A10=NULL;A2=9"}
]

Explanation

Lexicographic ordering is string-based, so A10 appears between A1 and A2.

Example 3

Input

Products:
[]

Output

[]

Explanation

No rows means no products and no store domain, so output is empty.

Constraints

0 <= number of rows in Products <= 1600
1 <= product_id <= 1000000000
1 <= len(store) <= 20
store contains only letters, digits, and underscore
0 <= price <= 1000000000
(product_id, store) is unique
Number of distinct stores per input <= 30
For each product, store_price_map must enumerate every global store in lexicographic order
Missing product-store combinations must be encoded as store=NULL
Return exactly columns: product_id, store_price_map
Sort final output by product_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly product_id and store_price_map