SQL Joins and Logic | Dataford Interview Questions

Tell me about a project where you used SQL joins and logic to transform data into something usable for downstream analytics.

Use the provided tables to produce a customer-level analytics dataset. Include customers with no completed orders and exclude cancelled or pending orders from completed-order metrics.

Output

One row per customer, including customers without completed orders
Columns: customer_id, customer_name, completed_orders, total_net_sales, latest_completed_order_date, and customer_segment
Classify customers as Preferred for net sales of at least 100, Standard for positive net sales below 100, and No activity otherwise
Order by total_net_sales descending, then customer_id ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer associated with the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order processing status
discount_pct	DECIMAL(5,2)	Discount expressed as a decimal fraction

order_items

Column	Type	Description
order_item_idPK	INT	Unique order item identifier
order_id	INT	Order containing the item
product_name	VARCHAR(100)	Name of the purchased product
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Price per unit

Your question is SQL Joins and Logic. 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