Quick 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.

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.

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.

Loading coding console...