Product Price Row Expansion by Store

Easy

A retail pricing pipeline stores one product per row and tracks prices across three storefront channels. Table: products product_id: unique identifier for a product store1: price in store channel 1 (NULL if unavailable) store2: price in store channel 2 (NULL if unavailable) store3: price in store channel 3 (NULL if unavailable) Task: Reshape this wide table into row-level price records so each output row describes one available listing: (product_id, store, price). Output requirements: Return exactly these columns: product_id store price Exclude any (product_id, store) pair where the price is NULL. Sort by product_id in ascending order. For the same product_id, sort stores in ascending lexical order (store1, store2, store3). Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

products:
| product_id | store1 | store2 | store3 |
|------------|--------|--------|--------|
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |

Output

[
  {"product_id":0,"store":"store1","price":95},
  {"product_id":0,"store":"store2","price":100},
  {"product_id":0,"store":"store3","price":105},
  {"product_id":1,"store":"store1","price":70},
  {"product_id":1,"store":"store3","price":80}
]

Explanation

Product 0 is available in all stores. Product 1 is unavailable in store2, so that row is omitted.

Example 2

Input

products:
| product_id | store1 | store2 | store3 |
|------------|--------|--------|--------|
| 9          | null   | 22     | null   |
| 2          | 15     | 18     | null   |

Output

[
  {"product_id":2,"store":"store1","price":15},
  {"product_id":2,"store":"store2","price":18},
  {"product_id":9,"store":"store2","price":22}
]

Explanation

Only non-null store prices are kept, and rows are sorted by product_id then store.

Example 3

Input

products:
| product_id | store1 | store2 | store3 |
|------------|--------|--------|--------|
| 10         | null   | null   | null   |
| 11         | null   | null   | null   |

Output

[]

Explanation

No product has availability in any store, so the output is empty.

Constraints

1 <= number of rows in products <= 200000
0 <= product_id <= 1000000000
product_id is unique
store1, store2, store3 are either NULL or integer prices
0 <= price <= 1000000000 for non-null price values
Return exactly columns: product_id, store, price
Exclude rows where price is NULL
Sort by product_id ASC, then store ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: product_id, store, price

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

Product Price Row Expansion by Store

Easy

A retail pricing pipeline stores one product per row and tracks prices across three storefront channels. Table: products product_id: unique identifier for a product store1: price in store channel 1 (NULL if unavailable) store2: price in store channel 2 (NULL if unavailable) store3: price in store channel 3 (NULL if unavailable) Task: Reshape this wide table into row-level price records so each output row describes one available listing: (product_id, store, price). Output requirements: Return exactly these columns: product_id store price Exclude any (product_id, store) pair where the price is NULL. Sort by product_id in ascending order. For the same product_id, sort stores in ascending lexical order (store1, store2, store3). Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

products:
| product_id | store1 | store2 | store3 |
|------------|--------|--------|--------|
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |

Output

[
  {"product_id":0,"store":"store1","price":95},
  {"product_id":0,"store":"store2","price":100},
  {"product_id":0,"store":"store3","price":105},
  {"product_id":1,"store":"store1","price":70},
  {"product_id":1,"store":"store3","price":80}
]

Explanation

Product 0 is available in all stores. Product 1 is unavailable in store2, so that row is omitted.

Example 2

Input

products:
| product_id | store1 | store2 | store3 |
|------------|--------|--------|--------|
| 9          | null   | 22     | null   |
| 2          | 15     | 18     | null   |

Output

[
  {"product_id":2,"store":"store1","price":15},
  {"product_id":2,"store":"store2","price":18},
  {"product_id":9,"store":"store2","price":22}
]

Explanation

Only non-null store prices are kept, and rows are sorted by product_id then store.

Example 3

Input

products:
| product_id | store1 | store2 | store3 |
|------------|--------|--------|--------|
| 10         | null   | null   | null   |
| 11         | null   | null   | null   |

Output

[]

Explanation

No product has availability in any store, so the output is empty.

Constraints

1 <= number of rows in products <= 200000
0 <= product_id <= 1000000000
product_id is unique
store1, store2, store3 are either NULL or integer prices
0 <= price <= 1000000000 for non-null price values
Return exactly columns: product_id, store, price
Exclude rows where price is NULL
Sort by product_id ASC, then store ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: product_id, store, price