Detect customers with suspicious refunds
SQL
Hard
2 views
Problem Description
Assume Payments.amount is negative for refunds. Find customers where total refund is more than 30% of total paid amount. Use conditional SUM.
Sample Test Case
Input:
Payments(order_id, amount), Orders(order_id, customer_id)
Constraints
Think: SUM refund and SUM paid separately
Official Solution
SELECT o.customer_id FROM Orders o JOIN Payments p ON p.order_id = o.order_id GROUP BY o.customer_id HAVING ABS(SUM(CASE WHEN p.amount < 0 THEN p.amount ELSE 0 END)) > 0.30 * SUM(CASE WHEN p.amount > 0 THEN p.amount ELSE 0 END);
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!