Calculate cost from orders with SQL

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.

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.

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
order_date	DATE	NOT NULL
quantity	INT	NOT NULL
unit_price	DECIMAL(10,2)	NOT NULL
coupon_code	VARCHAR(20)

Sample data:

coupon_code	order_date	order_id	quantity	unit_price	user_id
SAVE10	2025-05-10	1	2	50	101
SAVE10	2025-04-15	2	1	60	102
OLD5	2025-02-10	3	5	15	101
BIG50	2025-06-10	4	3	40	103
UNKNOWN	2025-05-20	5	2	30	104

Table coupons

column	type	constraints
code	VARCHAR(20)	PRIMARY KEY, NOT NULL
discount_pct	DECIMAL(5,2)	NOT NULL
max_discount	DECIMAL(10,2)	NOT NULL
valid_from	DATE	NOT NULL
valid_to	DATE	NOT NULL

Sample data:

code	discount_pct	max_discount	valid_from	valid_to
SAVE10	0.1	20	2025-05-01	2025-05-31
BIG50	0.5	30	2025-04-01	2025-06-30
OLD5	0.05	5	2025-01-01	2025-03-31

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.

final_cost	order_id	user_id
97.2	1	101
64.8	2	102
76.95	3	101
97.2	4	103
64.8	5	104

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.