Category Discounted Product Pricing

Easy

A commerce pricing engine stores product catalog prices and optional category-level discount policies. You are given two tables. Products product_id: unique product identifier category: product category label price: base product price Discounts category: category label discount: integer discount percentage in [0, 100] Task: For each product row, compute its discounted final price. Rules: Match discount by category. If a product category has no row in Discounts, use discount = 0. Use this formula: final_price = price * (100 - discount) / 100 Round final_price to 2 decimal places using HALF_UP behavior. Return requirements: Return one row per product. Return columns in exact order: product_id, final_price, category Sort by product_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Products:
| product_id | category    | price   |
|------------|-------------|---------|
| 1          | electronics | 1000.00 |
| 2          | apparel     | 50.00   |
| 3          | electronics | 1200.00 |
| 4          | home        | 500.00  |

Discounts:
| category    | discount |
|-------------|----------|
| electronics | 10       |
| apparel     | 20       |

Output

[
  {"product_id":1,"final_price":900.0,"category":"electronics"},
  {"product_id":2,"final_price":40.0,"category":"apparel"},
  {"product_id":3,"final_price":1080.0,"category":"electronics"},
  {"product_id":4,"final_price":500.0,"category":"home"}
]

Explanation

Products in missing categories keep original prices; others apply the matched discount percentage.

Example 2

Input

Products:
| product_id | category   | price |
|------------|------------|-------|
| 10         | premium    | 99.99 |
| 11         | clearance  | 20.00 |
| 12         | regular    | 45.50 |
| 13         | regular    | 0.01  |

Discounts:
| category   | discount |
|------------|----------|
| premium    | 0        |
| clearance  | 100      |

Output

[
  {"product_id":10,"final_price":99.99,"category":"premium"},
  {"product_id":11,"final_price":0.0,"category":"clearance"},
  {"product_id":12,"final_price":45.5,"category":"regular"},
  {"product_id":13,"final_price":0.01,"category":"regular"}
]

Explanation

0% keeps the original amount, 100% forces zero, and categories without policy default to 0%.

Example 3

Input

Products:
| product_id | category    | price |
|------------|-------------|-------|
| 21         | electronics | 20.05 |
| 22         | beauty      | 10.01 |
| 23         | beauty      | 3.33  |
| 24         | toys        | 7.77  |

Discounts:
| category    | discount |
|-------------|----------|
| electronics | 50       |
| beauty      | 33       |
| toys        | 34       |

Output

[
  {"product_id":21,"final_price":10.03,"category":"electronics"},
  {"product_id":22,"final_price":6.71,"category":"beauty"},
  {"product_id":23,"final_price":2.23,"category":"beauty"},
  {"product_id":24,"final_price":5.13,"category":"toys"}
]

Explanation

Rounding follows HALF_UP at 2 decimals for deterministic discount outcomes.

Constraints

1 <= number of rows in Products <= 250000
0 <= number of rows in Discounts <= 100000
Products.product_id values are unique
1 <= product_id <= 1000000000
category is a non-empty string
0 <= price <= 100000000 and price has at most 2 decimal places
Discounts.category is unique
0 <= discount <= 100
Missing category discounts must be treated as 0
Output columns must be exactly: product_id, final_price, category
final_price must be rounded to 2 decimals with HALF_UP behavior
SQL submissions are evaluated on SQLite 3.27.2
Pandas submissions must return a DataFrame with columns [product_id, final_price, category]

Loading problem...

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

Category Discounted Product Pricing

Easy

A commerce pricing engine stores product catalog prices and optional category-level discount policies. You are given two tables. Products product_id: unique product identifier category: product category label price: base product price Discounts category: category label discount: integer discount percentage in [0, 100] Task: For each product row, compute its discounted final price. Rules: Match discount by category. If a product category has no row in Discounts, use discount = 0. Use this formula: final_price = price * (100 - discount) / 100 Round final_price to 2 decimal places using HALF_UP behavior. Return requirements: Return one row per product. Return columns in exact order: product_id, final_price, category Sort by product_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Products:
| product_id | category    | price   |
|------------|-------------|---------|
| 1          | electronics | 1000.00 |
| 2          | apparel     | 50.00   |
| 3          | electronics | 1200.00 |
| 4          | home        | 500.00  |

Discounts:
| category    | discount |
|-------------|----------|
| electronics | 10       |
| apparel     | 20       |

Output

[
  {"product_id":1,"final_price":900.0,"category":"electronics"},
  {"product_id":2,"final_price":40.0,"category":"apparel"},
  {"product_id":3,"final_price":1080.0,"category":"electronics"},
  {"product_id":4,"final_price":500.0,"category":"home"}
]

Explanation

Products in missing categories keep original prices; others apply the matched discount percentage.

Example 2

Input

Products:
| product_id | category   | price |
|------------|------------|-------|
| 10         | premium    | 99.99 |
| 11         | clearance  | 20.00 |
| 12         | regular    | 45.50 |
| 13         | regular    | 0.01  |

Discounts:
| category   | discount |
|------------|----------|
| premium    | 0        |
| clearance  | 100      |

Output

[
  {"product_id":10,"final_price":99.99,"category":"premium"},
  {"product_id":11,"final_price":0.0,"category":"clearance"},
  {"product_id":12,"final_price":45.5,"category":"regular"},
  {"product_id":13,"final_price":0.01,"category":"regular"}
]

Explanation

0% keeps the original amount, 100% forces zero, and categories without policy default to 0%.

Example 3

Input

Products:
| product_id | category    | price |
|------------|-------------|-------|
| 21         | electronics | 20.05 |
| 22         | beauty      | 10.01 |
| 23         | beauty      | 3.33  |
| 24         | toys        | 7.77  |

Discounts:
| category    | discount |
|-------------|----------|
| electronics | 50       |
| beauty      | 33       |
| toys        | 34       |

Output

[
  {"product_id":21,"final_price":10.03,"category":"electronics"},
  {"product_id":22,"final_price":6.71,"category":"beauty"},
  {"product_id":23,"final_price":2.23,"category":"beauty"},
  {"product_id":24,"final_price":5.13,"category":"toys"}
]

Explanation

Rounding follows HALF_UP at 2 decimals for deterministic discount outcomes.

Constraints

1 <= number of rows in Products <= 250000
0 <= number of rows in Discounts <= 100000
Products.product_id values are unique
1 <= product_id <= 1000000000
category is a non-empty string
0 <= price <= 100000000 and price has at most 2 decimal places
Discounts.category is unique
0 <= discount <= 100
Missing category discounts must be treated as 0
Output columns must be exactly: product_id, final_price, category
final_price must be rounded to 2 decimals with HALF_UP behavior
SQL submissions are evaluated on SQLite 3.27.2
Pandas submissions must return a DataFrame with columns [product_id, final_price, category]