Cards That Never Spend at Home

"Never" is a statement about a whole group, so it cannot live in a WHERE clause. WHERE decides one row at a time. WHERE m.country <> a.country answers a different question — "has at least one foreign transaction" — and in this ledger that is almost everybody: 1,715 of 2,165 transactions cross a border.

The cards worth looking at are the ones that have never transacted at a merchant in the cardholder's own country. Express it as a condition on the group: count the home-country transactions with a conditional SUM and require that count to be zero in HAVING.

Return account_id, email, country (the cardholder's), txn_count, merchant_countries (distinct merchant countries they used) and total_spend (rounded to 2 decimals). 24 accounts qualify. Order by txn_count descending, then account_id ascending.

The filter must be HAVING SUM(CASE WHEN m.country = a.country THEN 1 ELSE 0 END) = 0 — a count of the rows you do NOT want, required to be zero. Filtering those rows out with WHERE first would delete the evidence you are testing for and every account would pass.