Bank With the Largest Sum of Each User's Biggest Single Transfer

Users of a trading app move money between the app and bank accounts they have linked to it. Two tables record the linked accounts and the transfers. For every user and every bank, find that user's largest single transfer at the bank. Then add those per-user maxima up by bank name, and return the bank with the largest total. One user can link several accounts, so one `user_id` can have several `bank_id` values, and two of a user's accounts can be at the same bank. The report singles this out as the detail that matters. It does not say whether "each bank" means each account or each bank name; this version fixes the rule in the Output Contract. ### Input Tables **`bank`**: one row per linked bank account. | Column | Type | Meaning | | --- | --- | --- | | `bank_id` | INTEGER | Primary key. Identifies one linked account, not a bank: several rows can share a `bank_name`. | | `user_id` | INTEGER | The user who linked the account. | | `bank_name` | TEXT | Name of the bank that holds the account. | **`fund_transfers`**: one row per transfer. | Column | Type | Meaning | | --- | --- | --- | | `transfer_id` | INTEGER | Primary key. | | `user_id` | INTEGER | The user who made the transfer. | | `bank_id` | INTEGER | The linked account used; references `bank.bank_id`. | | `amount` | NUMERIC(12, 2) | Transfer amount, always positive. | | `direction` | TEXT | `'out'` for money leaving the app to the bank account, `'in'` for money coming into the app from it. | ### Output Contract Write one read-only PostgreSQL query (a `SELECT`, optionally with `WITH` clauses) that returns exactly these columns: | Column | Meaning | | --- | --- | | `bank_name` | A bank with the largest total. | | `total_max_amount` | That bank's total, defined below. | - For each pair (`user_id`, `bank_name`) with at least one transfer, the user's maximum is the largest `amount` among that user's transfers through any of their accounts at that bank, in either direction. - A bank's `total_max_amount` is the sum of these maxima over all of its users. - Return every bank whose total equals the largest total, so a tie produces several rows. - Order the result by `bank_name`. ### Example `bank` | bank_id | user_id | bank_name | | --- | --- | --- | | 10 | 1 | Bank A | | 11 | 1 | Bank A | | 12 | 1 | Bank B | | 20 | 2 | Bank A | | 30 | 3 | Bank B | | 31 | 3 | Bank C | `fund_transfers` | transfer_id | user_id | bank_id | amount | direction | | --- | --- | --- | --- | --- | | 1 | 1 | 10 | 500.00 | in | | 2 | 1 | 11 | 700.00 | out | | 3 | 1 | 12 | 200.00 | in | | 4 | 2 | 20 | 300.00 | in | | 5 | 2 | 20 | 100.00 | out | | 6 | 3 | 30 | 900.00 | in | | 7 | 3 | 30 | 250.00 | out | Expected result: | bank_name | total_max_amount | | --- | --- | | Bank B | 1100.00 | User 1 has two accounts at Bank A, so their maximum there is 700.00, not 500.00 plus 700.00. Bank A's total is 700.00 + 300.00 = 1000.00, Bank B's is 200.00 + 900.00 = 1100.00, and Bank C has no transfers, so it has no total. ### Constraints and Clarifications - No column contains NULL. `direction` is always `'in'` or `'out'`, and both directions count. - Every `fund_transfers.bank_id` exists in `bank`, and `fund_transfers.user_id` always equals that account's `bank.user_id`. - Compare `bank_name` values exactly as stored; do not trim them or change their case. - When several of a user's transfers at one bank tie for the largest amount, that amount still counts once for the user. - A bank with no transfers does not appear. If `fund_transfers` is empty, return no rows. ```hint Fix the grain first Decide what one row represents after the first aggregation before writing the join, and check what a user with two accounts at the same bank contributes to that bank. ```