Calculate cost from orders with SQL
Company: MathWorks
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
You have two tables: orders(order_id INT, user_id INT, order_date DATE, quantity INT, unit_price DECIMAL, coupon_code VARCHAR) and coupons(code VARCHAR, discount_pct DECIMAL, max_discount DECIMAL, valid_from DATE, valid_to DATE). For each order, compute final_cost as follows: subtotal = quantity * unit_price; if coupon_code matches coupons.code and order_date ∈ [valid_from, valid_to], then discount = LEAST(subtotal * discount_pct, max_discount); otherwise discount = 0; tax = 0.08 * (subtotal - discount); final_cost = subtotal - discount + tax. Write SQL to output (order_id, user_id, final_cost) for all orders.
Overview: This question evaluates a candidate's ability to perform SQL data manipulation and business-rule calculations, including conditional joins, date-range filtering, and numeric arithmetic for discounts and taxes.
You are given two tables:
1) orders(order_id INT, user_id INT, order_date DATE, quantity INT, unit_price DECIMAL(10,2), coupon_code VARCHAR(20))
2) coupons(code VARCHAR(20), discount_pct DECIMAL(5,2), max_discount DECIMAL(10,2), valid_from DATE, valid_to DATE)
For each order, compute final_cost as follows:
- subtotal = quantity * unit_price
- If coupon_code matches coupons.code AND order_date is between valid_from and valid_to (inclusive), then:
discount = LEAST(subtotal * discount_pct, max_discount)
Otherwise:
discount = 0
- tax = 0.08 * (subtotal - discount)
- final_cost = subtotal - discount + tax
Write an SQL query to output (order_id, user_id, final_cost) for all orders.
Tables
orders(order_id INT, user_id INT, order_date DATE, quantity INT, unit_price DECIMAL(10,2), coupon_code VARCHAR(20))
coupons(code VARCHAR(20), discount_pct DECIMAL(5,2), max_discount DECIMAL(10,2), valid_from DATE, valid_to DATE)
Hints
- Use a LEFT JOIN from orders to coupons and include the date-range condition in the join.
- Compute subtotal and discount in a subquery or CTE, then apply the tax formula to get final_cost.