SQL Return Percentage Query | Dataford Interview Questions

ABC Commerce wants to identify customers with unusually high return activity. Write a PostgreSQL query that returns customers whose return percentage is greater than 50%.

Requirements

Count each customer's total orders from orders.
Count distinct returned orders from returns, while preserving customers with no returns during aggregation.
Calculate return_percentage as returned orders divided by total orders, multiplied by 100, rounded to two decimals.
Return only customers whose percentage is strictly greater than 50%, ordered from highest to lowest percentage and then by customer ID.

Assume each non-null returns.order_id identifies a returned order. A return row with a null order ID is invalid for matching and must not increase any customer's return count.

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	ABC Commerce customer identifier
customer_name	VARCHAR(100)	Customer display name
order_date	DATE	Date the order was placed

returns

Column	Type	Description
return_idPK	INT	Unique return record identifier
order_id	INT	Order associated with the return, when known
returned_at	DATE	Date the order was returned
return_reason	VARCHAR(100)	Reason for the return

Your question is SQL Return Percentage Query. Start with the requirements and the two 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