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.

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

  1. Use a LEFT JOIN from orders to coupons and include the date-range condition in the join.
  2. Compute subtotal and discount in a subquery or CTE, then apply the tax formula to get final_cost.
MathWorksSoftware EngineermediumData Manipulation (SQL/Python)

Calculate Final Order Cost with Coupons and Tax

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.

Expected Output

One row per order, showing the order_id, user_id, and the computed final_cost after applying any valid coupon discount and 8% tax on the post-discount amount.

Tables

orderscoupons
orders
ColumnType
order_idINT
user_idINT
order_dateDATE
quantityINT
unit_priceDECIMAL(10,2)
coupon_codeVARCHAR(20)
coupons
ColumnType
codeVARCHAR(20)
discount_pctDECIMAL(5,2)
max_discountDECIMAL(10,2)
valid_fromDATE
valid_toDATE

Comments (0)

No comments yet. Be the first to comment!

Submit your answer

Approved first answer today earns 30 XP
Bold⌘BItalic⌘IInline code⌘`Bullet listTab to nest (○ ■)Numbered listTab to nest (a. i.)QuoteCode block
Formatting help
Language
Run code to see results here.

Browse More Questions