Adaptive Product Price Unpivot by Active Store Set

Hard

A commerce data platform stores product prices in a wide table (one store channel per column), but downstream services consume row-level records. Each export batch also carries an active store list that controls which channels should be emitted. Table: products product_id: unique product identifier store_north: nullable price for north region channel store_south: nullable price for south region channel store_east: nullable price for east region channel store_west: nullable price for west region channel store_online: nullable price for online channel store_outlet: nullable price for outlet channel Table: active_stores store: enabled store column name for this export run Business rules: Keep only stores listed in active_stores. active_stores can contain duplicate rows and must be handled correctly. A NULL price means the product is unavailable in that store and should not produce output. Task: Return normalized row-level records for active and available prices. Output requirements: Return exactly: product_id, store, price Sort by product_id ascending, then store ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

products:
| product_id | store_north | store_south | store_east | store_west | store_online | store_outlet |
|------------|-------------|-------------|------------|------------|--------------|--------------|
| 1          | 120         | null        | 100        | null       | 95           | null         |
| 2          | null        | 80          | null       | 70         | null         | 60           |

active_stores:
| store        |
|--------------|
| store_east   |
| store_online |
| store_outlet |

Output

[
  {"product_id":1,"store":"store_east","price":100},
  {"product_id":1,"store":"store_online","price":95},
  {"product_id":2,"store":"store_outlet","price":60}
]

Explanation

Only active stores are considered. Null store values are excluded.

Example 2

Input

products:
| product_id | store_north | store_south | store_east | store_west | store_online | store_outlet |
|------------|-------------|-------------|------------|------------|--------------|--------------|
| 10         | 15          | 20          | 25         | 30         | 35           | 40           |

active_stores:
| store       |
|-------------|
| store_west  |
| store_west  |
| store_north |

Output

[
  {"product_id":10,"store":"store_north","price":15},
  {"product_id":10,"store":"store_west","price":30}
]

Explanation

Duplicate active store rows should not duplicate output rows.

Example 3

Input

products:
[]

active_stores:
| store        |
|--------------|
| store_east   |
| store_online |

Output

[]

Explanation

No product rows means no output rows.

Constraints

0 <= number of rows in products <= 250
0 <= number of rows in active_stores <= 80
1 <= product_id <= 2000000000
product_id values are unique within products
Each non-null price is an integer in [1, 1000000]
active_stores.store is one of: store_north, store_south, store_east, store_west, store_online, store_outlet
active_stores may contain duplicate rows
Exclude rows where selected store price is NULL
Return exactly columns: product_id, store, price
Sort by product_id ascending, then store ascending
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.

Adaptive Product Price Unpivot by Active Store Set

Hard

A commerce data platform stores product prices in a wide table (one store channel per column), but downstream services consume row-level records. Each export batch also carries an active store list that controls which channels should be emitted. Table: products product_id: unique product identifier store_north: nullable price for north region channel store_south: nullable price for south region channel store_east: nullable price for east region channel store_west: nullable price for west region channel store_online: nullable price for online channel store_outlet: nullable price for outlet channel Table: active_stores store: enabled store column name for this export run Business rules: Keep only stores listed in active_stores. active_stores can contain duplicate rows and must be handled correctly. A NULL price means the product is unavailable in that store and should not produce output. Task: Return normalized row-level records for active and available prices. Output requirements: Return exactly: product_id, store, price Sort by product_id ascending, then store ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

products:
| product_id | store_north | store_south | store_east | store_west | store_online | store_outlet |
|------------|-------------|-------------|------------|------------|--------------|--------------|
| 1          | 120         | null        | 100        | null       | 95           | null         |
| 2          | null        | 80          | null       | 70         | null         | 60           |

active_stores:
| store        |
|--------------|
| store_east   |
| store_online |
| store_outlet |

Output

[
  {"product_id":1,"store":"store_east","price":100},
  {"product_id":1,"store":"store_online","price":95},
  {"product_id":2,"store":"store_outlet","price":60}
]

Explanation

Only active stores are considered. Null store values are excluded.

Example 2

Input

products:
| product_id | store_north | store_south | store_east | store_west | store_online | store_outlet |
|------------|-------------|-------------|------------|------------|--------------|--------------|
| 10         | 15          | 20          | 25         | 30         | 35           | 40           |

active_stores:
| store       |
|-------------|
| store_west  |
| store_west  |
| store_north |

Output

[
  {"product_id":10,"store":"store_north","price":15},
  {"product_id":10,"store":"store_west","price":30}
]

Explanation

Duplicate active store rows should not duplicate output rows.

Example 3

Input

products:
[]

active_stores:
| store        |
|--------------|
| store_east   |
| store_online |

Output

[]

Explanation

No product rows means no output rows.

Constraints

0 <= number of rows in products <= 250
0 <= number of rows in active_stores <= 80
1 <= product_id <= 2000000000
product_id values are unique within products
Each non-null price is an integer in [1, 1000000]
active_stores.store is one of: store_north, store_south, store_east, store_west, store_online, store_outlet
active_stores may contain duplicate rows
Exclude rows where selected store price is NULL
Return exactly columns: product_id, store, price
Sort by product_id ascending, then store ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: product_id, store, price