Сначала попробуйте сами: на собеседовании подсказки не будет.
Сумму успешных платежей считаем отдельно в CTE, по одной строке на заказ. Заказы соединяем с ней LEFT JOIN, иначе заказы без платежей исчезнут, хотя по условию у них paid = 0 и долг равен всей сумме.
После LEFT JOIN у таких заказов paid NULL, поэтому везде coalesce(p.paid, 0). Фильтр по остатку стоит в WHERE снаружи соединения, lower(status) из-за регистра.
WITH paid AS (SELECT order_id, sum(amount) AS paid FROM payments WHERE status = 'success' GROUP BY order_id) SELECT o.order_id, o.total_amount, coalesce(p.paid, 0) AS paid, o.total_amount - coalesce(p.paid, 0) AS due FROM orders o LEFT JOIN paid p ON p.order_id = o.order_id WHERE lower(o.status) IN ('shipped', 'delivered') AND