SQL Challenges · WEEKLY QUACK CHALLENGE!
THE DOUBLE DIP
Difficulty: medium
Refund Clerk Redhead slides a ledger across the counter. Returns are fine, Duckbert. Paying a duck back more than they paid for an order is less fine. Pearl returned half the shop, Betty paid in three installments, and somebody swears their other purchases should make us even. I need the customer who collected the most excess refunds across their orders. Somebodys left with their bread and ours — find them before I balance this till!
Your task
Return the customer name with the largest total excess refund. For each order, total its `succeeded` payments and its `succeeded` refunds; `failed` and `pending` attempts do not count. Count every successful row, even when two share an amount, and an order with no successful payment has been paid zero. An order's excess is how much its refunds exceed its payments, or zero if they do not. Add up the excess across each customer's orders. On a tie, return the alphabetically first customer name. You will be working with the `orders`, `payments`, and `refunds` tables.
Tables
orders (14 rows)
| column | type |
|---|---|
order_id | INTEGER |
customer_name | VARCHAR |
payments (21 rows)
| column | type |
|---|---|
payment_id | INTEGER |
order_id | INTEGER |
amount | DECIMAL(10,2) |
status | VARCHAR |
refunds (16 rows)
| column | type |
|---|---|
refund_id | INTEGER |
order_id | INTEGER |
amount | DECIMAL(10,2) |
status | VARCHAR |
Database: https://dbquacks.com/data/databases/weekly_202641_15_double_dip.duckdb
This page as markdown: https://dbquacks.com/challenges/weekly/15.md