Dynamic Store Price Pivot Map

HARD30 pts

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

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

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

Input

Products:
[]

Output

[]

Explanation

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

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Products =

[{"product_id":1,"store":"Shop","price":110},{"product_id":3,"store":"Shop","price":1000},{"product_id":2,"store":"Souq","price":190},{"product_id":3,"store":"Souq","price":1900},{"product_id":2,"store":"Nozama","price":200},{"product_id":1,"store":"LC_Store","price":100}]

Dynamic Store Price Pivot Map

Hints

Dynamic Store Price Pivot Map

Hints