Calculate cost from orders with SQL
Company: MathWorks
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Take-home Project
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.
Quick Answer: 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.