Store Price Matrix by Product

A retail pricing warehouse stores one row per (product, store) offer. Analysts need a fixed-format report that places each store's price in its own column so downstream dashboards can compare stores side by side.

Table: products

• product_id: unique product identifier
• store: store label, always one of "store1", "store2", "store3"
• price: listed price of that product in that store

Data guarantees:

• (product_id, store) is unique.
• A product can appear in one, two, or all three stores.
• Missing store entries mean that product is not currently sold in that store.

Task:

• Return one row per product_id.
• Pivot store values into three fixed columns: store1, store2, store3.
• If a product is absent in a store, return NULL for that store column.

Output requirements:

• Return exactly these columns in this order: product_id, store1, store2, store3
• Recommended deterministic ordering: product_id ascending.

Supported submission environments:

• SQL (SQLite 3.27.2)
• Pandas (Python)

products:
| product_id | store  | 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":null,"store3":80}
]

products:
| product_id | store  | price |
|------------|--------|-------|
| 11         | store2 | 45    |
| 11         | store1 | 30    |
| 12         | store3 | 90    |
| 13         | store2 | 0     |

[
  {"product_id":11,"store1":30,"store2":45,"store3":null},
  {"product_id":12,"store1":null,"store2":null,"store3":90},
  {"product_id":13,"store1":null,"store2":0,"store3":null}
]

products:
| product_id | store  | price |
|------------|--------|-------|
| (no rows)  |

[]