Joining Tables With Sales Filters | Dataford Interview Questions

HARMAN's Business Analytics team wants a quarterly view of qualifying North American sales across JBL, Harman Kardon, and related product lines. Write a PostgreSQL query that joins orders, order items, and products to identify products meeting the specified sales criteria.

Requirements

Include only orders from North America with status Completed between 2025-01-01 and 2025-03-31.
Include only products in the Audio category.
Calculate total units, distinct order count, and net revenue after discounts, treating a missing discount as 0%.
Keep products with net revenue of at least 250, label each result using CASE, and sort by net revenue descending.

Schema

sales_orders

Column	Type	Description
order_idPK	INT	Unique sales order identifier
order_date	DATE	Date the order was placed
region	VARCHAR(50)	Geographic sales region
status	VARCHAR(20)	Order processing status

order_items

Column	Type	Description
item_idPK	INT	Unique order item identifier
order_id	INT	References sales_orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units sold
unit_price	NUMERIC(10,2)	Price per unit before discount
discount_percent	NUMERIC(5,2)	Discount percentage applied to the item

products

Column	Type	Description
product_idPK	INT	Unique HARMAN product identifier
product_name	VARCHAR(150)	HARMAN product name
category	VARCHAR(50)	Product category

Your question is Joining Tables With Sales Filters. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem