Customers Holding Both Savings and Current Accounts
The bank is launching a sweep facility that links a customer's current account to their savings account.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #54
Problem statement
The bank is launching a sweep facility that links a customer's current account to their savings account. It is offered only to customers who hold at least one SAVINGS and at least one CURRENT account. Salary accounts do not count as either.
Return the customer_id of every such customer, ordered by customer_id.
Tables
Table: BankAccount
| Column | Type |
|---|---|
| account_id | int |
| customer_id | int |
| account_type | enum(SAVINGS, CURRENT, SALARY) |
| opened_on | date |
Primary key: account_id.
One row per account. A customer may hold several accounts of the same type.
Examples
Example 1
BankAccount
| account_id | customer_id | account_type | opened_on |
|---|---|---|---|
| 1 | 11 | SAVINGS | 2021-04-12 |
| 2 | 11 | CURRENT | 2022-07-01 |
| 3 | 12 | SAVINGS | 2020-01-15 |
| 4 | 12 | SALARY | 2023-06-30 |
| 5 | 13 | CURRENT | 2019-11-05 |
| 6 | 13 | CURRENT | 2024-02-02 |
| 7 | 14 | SAVINGS | 2018-09-09 |
| 8 | 14 | SAVINGS | 2021-03-03 |
| 9 | 14 | CURRENT | 2024-01-20 |
Output
| customer_id |
|---|
| 11 |
| 14 |
How to solve Customers Holding Both Savings and Current Accounts
Group the accounts by customer and ask two questions of each group: does it contain a savings account, and does it contain a current account? Conditional aggregation answers both in one pass — SUM(CASE WHEN account_type = 'SAVINGS' THEN 1 ELSE 0 END) counts the savings accounts in the group — and HAVING keeps the groups where both counts are positive. Several accounts of one type are harmless, since only "more than zero" matters.
A tempting shortcut is HAVING COUNT(DISTINCT account_type) = 2, but with three account types a customer holding savings and salary accounts also has two distinct types. It becomes correct once the WHERE clause keeps only savings and current rows, which is the first alternative.
A self join of savings rows to current rows of the same customer, or an INTERSECT of the two customer lists, express the same set. The grouped version is a single scan with a hash or sort on customer_id.
Reference solution (MySQL)
SELECT customer_id
FROM BankAccount
GROUP BY customer_id
HAVING SUM(CASE WHEN account_type = 'SAVINGS' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN account_type = 'CURRENT' THEN 1 ELSE 0 END) > 0
ORDER BY customer_idAnother way
SELECT customer_id FROM BankAccount WHERE account_type IN ('SAVINGS', 'CURRENT') GROUP BY customer_id HAVING COUNT(DISTINCT account_type) = 2 ORDER BY customer_idAnother way
SELECT DISTINCT s.customer_id FROM BankAccount s JOIN BankAccount c ON c.customer_id = s.customer_id AND c.account_type = 'CURRENT' WHERE s.account_type = 'SAVINGS' ORDER BY s.customer_idAnother way
SELECT customer_id FROM BankAccount WHERE account_type = 'SAVINGS' INTERSECT SELECT customer_id FROM BankAccount WHERE account_type = 'CURRENT' ORDER BY customer_id← Loan Applicants by Credit Score Band · Savings Accounts Above the Bank-Wide Average Balance →