PhonePe: Users With Three or More Active Linked Bank Accounts

Easy
875.

PhonePe: Users With Three or More Active Linked Bank Accounts

EasyAggregation

MySQL

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

ColumnType
link_idint
user_idint
bank_namevarchar
linked_ondate
is_activebool

Primary key: link_id.

One row per bank account a user has linked; is_active is 0 once the user unlinks it.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

LinkedAccount

link_iduser_idbank_namelinked_onis_active
111SBI2023-04-021
211HDFC2023-09-181
311HDFC2024-01-071
412ICICI2023-06-111
512Axis2023-07-211
612Kotak2024-02-140
713SBI2022-12-301
813PNB2023-03-031
913BOB2024-05-051
1013Canara2024-08-091

Output

user_idactive_accounts
113
134
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.

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

ColumnType
link_idint
user_idint
bank_namevarchar
linked_ondate
is_activebool

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_iduser_idbank_namelinked_onis_active
111SBI2023-04-021
211HDFC2023-09-181
311HDFC2024-01-071
412ICICI2023-06-111
512Axis2023-07-211
612Kotak2024-02-140
713SBI2022-12-301
813PNB2023-03-031
913BOB2024-05-051
1013Canara2024-08-091

Output

user_idactive_accounts
113
134

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(*) >= 3

Another way

SELECT user_id, SUM(is_active) AS active_accounts FROM LinkedAccount GROUP BY user_id HAVING SUM(is_active) >= 3

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