Customer order totals and latest order amount (SQL)

You have tables of customers, their orders and returned orders. Write one query that returns a row for every customer, including those with no orders, with their order count, total amount spent, the date of their most recent order and that order's amount.

Interface

Unless you agree on a different interface with the interviewer, assume these tables:

• customers(customer_id, customer_name)
• orders(order_id, customer_id, order_date, amount)
• returns(order_id, return_date) — an order appears here if it was returned.

Return customer_id, customer_name, total_orders, total_amount, latest_order_date and latest_order_amount, one row per customer. Count every order for now; returns come up as a follow-up.

Example

customers: (1, Ada), (2, Ben), (3, Cy)
orders:    (10, 1, 2025-01-05, 40), (11, 1, 2025-02-10, 25), (12, 2, 2025-01-20, 90)

customer_id  customer_name  total_orders  total_amount  latest_order_date  latest_order_amount
1            Ada            2             65            2025-02-10         25
2            Ben            1             90            2025-01-20         90
3            Cy             0             0             NULL               NULL

Constraints

• A customer can have many orders, several on the same date.
• Joining returns must not double-count orders.

Clarifying questions

• When a customer has two orders on their latest date, which amount is the latest one?
• Should a customer with no orders show 0 or NULL for totals?
• Do returned orders count toward the totals?

What a strong answer covers

• Aggregate orders per customer in one CTE, and pick the latest order per customer in another with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC).
• LEFT JOIN both from customers so customers without orders stay, with COALESCE for zero counts and amounts.
• A stated tie rule for the latest order, made deterministic in the window's ORDER BY.
• Why joining orders to returns before aggregating can inflate sums, and how to avoid it.

Follow-ups

• Exclude returned orders from the totals and from the latest order (NOT EXISTS against returns).
• Return every order on the latest date when there is a tie.
• Add each customer's average order value and days since their latest order.