SQL for Customer Returns | Dataford Interview Questions

ABC Education uses its customer operations data to identify customers with unusually high return activity. Write a PostgreSQL query that returns customers whose percentage of delivered orders with an approved or refunded return exceeds 50%.

Requirements

Consider only orders with order_status = 'delivered'.
Count an order at most once as returned, even if it has multiple return records.
Return the customer ID, name, region, delivered order count, returned order count, return percentage rounded to two decimals, and rank within the customer's region.
Exclude customers with no delivered orders, and sort by return percentage descending, then customer ID ascending.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer's full name
email	VARCHAR(255)	Customer email address
region	VARCHAR(50)	ABC Education operating region

orders

Column	Type	Description
order_idPK	INTEGER	Unique order identifier
customer_id	INTEGER	Customer who placed the order
order_status	VARCHAR(20)	Order lifecycle status

returns

Column	Type	Description
return_idPK	INTEGER	Unique return record identifier
order_id	INTEGER	Order associated with the return
return_status	VARCHAR(20)	Return processing status

Your question is SQL for Customer Returns. 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