PhonePe: Users With Three or More Active Linked Bank Accounts
A user can link several bank accounts to the app and later unlink some.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #875
Problem statement
A user can link several bank accounts to the app and later unlink some. The product team studying multi-bank users wants everyone who currently has at least three active linked accounts.
Return user_id and active_accounts, the number of that user's links with is_active = 1. Two accounts at the same bank are two links and both count. Unlinked (inactive) rows are ignored. Return the rows in any order.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: LinkedAccount
| Column | Type |
|---|---|
| link_id | int |
| user_id | int |
| bank_name | varchar |
| linked_on | date |
| is_active | bool |
Primary key: link_id.
One row per bank account a user has linked; is_active is 0 once the user unlinks it.
Examples
Example 1
LinkedAccount
| link_id | user_id | bank_name | linked_on | is_active |
|---|---|---|---|---|
| 1 | 11 | SBI | 2023-04-02 | 1 |
| 2 | 11 | HDFC | 2023-09-18 | 1 |
| 3 | 11 | HDFC | 2024-01-07 | 1 |
| 4 | 12 | ICICI | 2023-06-11 | 1 |
| 5 | 12 | Axis | 2023-07-21 | 1 |
| 6 | 12 | Kotak | 2024-02-14 | 0 |
| 7 | 13 | SBI | 2022-12-30 | 1 |
| 8 | 13 | PNB | 2023-03-03 | 1 |
| 9 | 13 | BOB | 2024-05-05 | 1 |
| 10 | 13 | Canara | 2024-08-09 | 1 |
Output
| user_id | active_accounts |
|---|---|
| 11 | 3 |
| 13 | 4 |
How to solve PhonePe: Users With Three or More Active Linked Bank Accounts
Each row is one link, so the number of active links per user is a count of rows after dropping the inactive ones: filter with WHERE is_active = 1, group by user_id, and count. The threshold applies to the count, which only exists after grouping, so it goes in HAVING COUNT(*) >= 3.
Nothing is deduplicated by bank — the statement says two accounts at the same bank both count — so COUNT(*) is right and COUNT(DISTINCT bank_name) would be wrong for user 11 in the example. A user whose links are all inactive has no rows left after WHERE and never forms a group.
Because is_active is 0 or 1, summing it counts the active links without a WHERE, which is the first alternative; the second does the conditional count in a derived table and filters outside it. All three are one scan and a hash aggregate over users.
Reference solution (MySQL)
SELECT user_id, COUNT(*) AS active_accounts
FROM LinkedAccount
WHERE is_active = 1
GROUP BY user_id
HAVING COUNT(*) >= 3Another way
SELECT user_id, SUM(is_active) AS active_accounts FROM LinkedAccount GROUP BY user_id HAVING SUM(is_active) >= 3Another way
SELECT user_id, active_accounts FROM (SELECT user_id, SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_accounts FROM LinkedAccount GROUP BY user_id) t WHERE active_accounts >= 3← PhonePe: QR Stickers That Never Took a Successful Payment · PhonePe: In-App Insurance Premium by Product Line in 2025 →