By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE customer (
`cust_id` INTEGER,
`state` VARCHAR(2)
);
INSERT INTO customer
(`cust_id`, `state`)
VALUES
('1', 'MA'),
('2', 'MA'),
('3', 'MA'),
('4', 'MA'),
('5', 'NH'),
('6', 'MA'),
('7', 'MA'),
('8', 'NH'),
('9', 'MA'),
('10', 'NH'),
('11', 'MA'),
('12', 'NH'),
('13', 'MA');
CREATE TABLE account (
`account_id` INTEGER,
`cust_id` INTEGER,
`avail_balance` FLOAT
);
INSERT INTO account
(`account_id`, `cust_id`, `avail_balance`)
VALUES
('1', '1', '1057.75'),
('2', '1', '500'),
('3', '1', '3000'),
('4', '2', '2258.02'),
('5', '2', '200'),
SELECT a.cust_id
FROM account a
INNER JOIN customer c ON a.cust_id = c.cust_id
INNER JOIN (
SELECT MAX(c.cust_id) AS max_nh_cust_id,
MAX(a.avail_balance) AS max_nh_avail_balance
FROM account a INNER JOIN customer c
ON a.cust_id = c.cust_id
WHERE c.state = 'NH'
) t ON c.cust_id > t.max_nh_cust_id AND a.avail_balance > t.max_nh_avail_balance
WHERE c.state = 'MA'
| cust_id |
|---|
| 13 |