Top 10 Customers by LTV | Dataford Interview Questions

Write a SQL query to retrieve the top 10 customers based on their lifetime value.

Use the customers and orders tables. Lifetime value is the sum of qualifying order totals, excluding orders with cancelled or refunded status. Customers without qualifying orders should have a lifetime value of zero.

Output

One row per customer, with customer_id, customer_name, and lifetime_value.
Return at most 10 customers, ordered by lifetime value descending, with customer_id ascending as the tie-breaker.

Schema

customers

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

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer associated with the order
order_total	DECIMAL(12,2)	Total monetary value of the order
status	VARCHAR(20)	Current order status

Your question is Top 10 Customers by LTV. 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