Customers Holding Both Savings and Current Accounts

Easy
54.

Customers Holding Both Savings and Current Accounts

EasyAggregation

MySQL

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

ColumnTypeValues
account_idint
customer_idint
account_typeenumSAVINGS, CURRENT, SALARY
opened_ondate

Primary key: account_id.

One row per account. A customer may hold several accounts of the same type.

Example

The rows must come back in the order shown.

BankAccount

account_idcustomer_idaccount_typeopened_on
111SAVINGS2021-04-12
211CURRENT2022-07-01
312SAVINGS2020-01-15
412SALARY2023-06-30
513CURRENT2019-11-05
613CURRENT2024-02-02
714SAVINGS2018-09-09
814SAVINGS2021-03-03
914CURRENT2024-01-20

Output

customer_id
11
14
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
account_idint
customer_idint
account_typeenum(SAVINGS, CURRENT, SALARY)
opened_ondate

Primary key: account_id.

One row per account. A customer may hold several accounts of the same type.

Examples

Example 1

BankAccount

account_idcustomer_idaccount_typeopened_on
111SAVINGS2021-04-12
211CURRENT2022-07-01
312SAVINGS2020-01-15
412SALARY2023-06-30
513CURRENT2019-11-05
613CURRENT2024-02-02
714SAVINGS2018-09-09
814SAVINGS2021-03-03
914CURRENT2024-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_id

Another 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_id

Another 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_id

Another 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 →