SQL Query From Multiple Tables | Dataford Interview Questions

Your question is SQL Query From Multiple Tables. Start with the requirements and the two tables on the right.

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

Problem

The Guidewire Cloud customer success team needs a customer-level view of submitted sales orders. Write a PostgreSQL query that lists every active customer and any matching submitted sales order number.

Requirements

Return the customer's name and the sales order number.
Include active customers who have no submitted sales orders, showing NULL for the order number.
Exclude inactive customers and sales orders whose status is not SUBMITTED.
Sort by customer name, then sales order number, with customers without orders appearing after matched orders.

Place the sales order status condition so that customers without qualifying orders remain in the result.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
customer_status	VARCHAR(20)	Customer lifecycle status
email	VARCHAR(255)	Primary customer email

sales_orders

Column	Type	Description
sales_order_idPK	INTEGER	Unique sales order identifier
customer_id	INTEGER	Customer associated with the order
sales_order_number	VARCHAR(30)	Business-facing sales order number
order_status	VARCHAR(20)	Order processing status