Product Yearly Price Report

Easy

Your analytics team maintains two relational tables: sales sale_id: sale event identifier product_id: item identifier sold in that row year: calendar year of the sale quantity: units sold in that event price: unit price captured for that sale product product_id: product identifier product_name: readable product label Task: Generate a sales report that includes, for each sale row, the product name together with the recorded year and unit price. Output schema (exact column order): product_name year price Notes: Join the two tables using product_id. Return one result row per matching sale row. Output row ordering is not constrained unless your local checker requires deterministic sorting. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |

product:
| product_id | product_name |
|------------|--------------|
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |

Output

[
  {"product_name":"Apple","year":2011,"price":9000},
  {"product_name":"Nokia","year":2008,"price":5000},
  {"product_name":"Nokia","year":2009,"price":5000}
]

Explanation

Each sale is enriched with its matching product_name by product_id. Unsold products in product are irrelevant to the result.

Example 2

Input

sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 11      | 10         | 2020 | 2        | 300   |
| 12      | 20         | 2021 | 1        | 700   |
| 13      | 10         | 2021 | 5        | 350   |

product:
| product_id | product_name |
|------------|--------------|
| 10         | Router       |
| 20         | Switch       |
| 30         | Rack         |

Output

[
  {"product_name":"Router","year":2020,"price":300},
  {"product_name":"Router","year":2021,"price":350},
  {"product_name":"Switch","year":2021,"price":700}
]

Explanation

Multiple sales can map to the same product_id across years. Each matching sale row must appear once.

Example 3

Input

sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 21      | 501        | 2019 | 10       | 1200  |
| 22      | 502        | 2019 | 8        | 1300  |

product:
| product_id | product_name |
|------------|--------------|
| 501        | Bundle       |
| 502        | Bundle       |

Output

[
  {"product_name":"Bundle","year":2019,"price":1200},
  {"product_name":"Bundle","year":2019,"price":1300}
]

Explanation

Join keys are product IDs, not product names. Different IDs can legitimately share the same product_name.

Constraints

1 <= number of rows in sales <= 10,000
1 <= number of rows in product <= 10,000
sales.product_id references product.product_id
product.product_id is unique
1 <= year <= 2100
1 <= quantity <= 100,000
1 <= price <= 1,000,000,000
Return output columns exactly as: product_name, year, price
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns [product_name, year, price]

Loading problem...

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

Product Yearly Price Report

Easy

Your analytics team maintains two relational tables: sales sale_id: sale event identifier product_id: item identifier sold in that row year: calendar year of the sale quantity: units sold in that event price: unit price captured for that sale product product_id: product identifier product_name: readable product label Task: Generate a sales report that includes, for each sale row, the product name together with the recorded year and unit price. Output schema (exact column order): product_name year price Notes: Join the two tables using product_id. Return one result row per matching sale row. Output row ordering is not constrained unless your local checker requires deterministic sorting. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |

product:
| product_id | product_name |
|------------|--------------|
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |

Output

[
  {"product_name":"Apple","year":2011,"price":9000},
  {"product_name":"Nokia","year":2008,"price":5000},
  {"product_name":"Nokia","year":2009,"price":5000}
]

Explanation

Each sale is enriched with its matching product_name by product_id. Unsold products in product are irrelevant to the result.

Example 2

Input

sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 11      | 10         | 2020 | 2        | 300   |
| 12      | 20         | 2021 | 1        | 700   |
| 13      | 10         | 2021 | 5        | 350   |

product:
| product_id | product_name |
|------------|--------------|
| 10         | Router       |
| 20         | Switch       |
| 30         | Rack         |

Output

[
  {"product_name":"Router","year":2020,"price":300},
  {"product_name":"Router","year":2021,"price":350},
  {"product_name":"Switch","year":2021,"price":700}
]

Explanation

Multiple sales can map to the same product_id across years. Each matching sale row must appear once.

Example 3

Input

sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 21      | 501        | 2019 | 10       | 1200  |
| 22      | 502        | 2019 | 8        | 1300  |

product:
| product_id | product_name |
|------------|--------------|
| 501        | Bundle       |
| 502        | Bundle       |

Output

[
  {"product_name":"Bundle","year":2019,"price":1200},
  {"product_name":"Bundle","year":2019,"price":1300}
]

Explanation

Join keys are product IDs, not product names. Different IDs can legitimately share the same product_name.

Constraints

1 <= number of rows in sales <= 10,000
1 <= number of rows in product <= 10,000
sales.product_id references product.product_id
product.product_id is unique
1 <= year <= 2100
1 <= quantity <= 100,000
1 <= price <= 1,000,000,000
Return output columns exactly as: product_name, year, price
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns [product_name, year, price]