PhonePe: Users Who Only Send, Only Receive or Do Both in October

Medium
889.

PhonePe: Users Who Only Send, Only Receive or Do Both in October

The P2P growth team splits October's active users by how they took part in person-to-person transfers: people who only sent money, people who only received it, and people who did both — the two-way users they most want to grow.

Consider only successful transfers with sent_on in October 2025. Return user_id and user_type for every user who sent or received at least one such transfer, where user_type is both for a user who sent and received, sender_only for one who sent but never received, and receiver_only for one who received but never sent. Failed transfers and transfers outside October do not count for either side. 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: P2pTransfer

ColumnTypeValues
transfer_idint
from_userint
to_userint
amountint
statusenumsuccess, failed
sent_ondate

Primary key: transfer_id.

One row per transfer from from_user to to_user; the two are always different users.

Example

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

P2pTransfer

transfer_idfrom_userto_useramountstatussent_on
112500success2025-10-01
223200success2025-10-05
331150failed2025-10-08
4451000success2025-10-31
556300success2025-09-30
661700success2025-11-01
714250success2025-10-20

Output

user_iduser_type
2both
4both
1sender_only
3receiver_only
5receiver_only
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 Who Only Send, Only Receive or Do Both in October

The P2P growth team splits October's active users by how they took part in person-to-person transfers: people who only sent money, people who only received…

  • Difficulty: Medium
  • Topics: Subqueries, Conditional Logic
  • Dialect: MySQL
  • Problem: #889

Problem statement

The P2P growth team splits October's active users by how they took part in person-to-person transfers: people who only sent money, people who only received it, and people who did both — the two-way users they most want to grow.

Consider only successful transfers with sent_on in October 2025. Return user_id and user_type for every user who sent or received at least one such transfer, where user_type is both for a user who sent and received, sender_only for one who sent but never received, and receiver_only for one who received but never sent. Failed transfers and transfers outside October do not count for either side. 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: P2pTransfer

ColumnType
transfer_idint
from_userint
to_userint
amountint
statusenum(success, failed)
sent_ondate

Primary key: transfer_id.

One row per transfer from from_user to to_user; the two are always different users.

Examples

Example 1

P2pTransfer

transfer_idfrom_userto_useramountstatussent_on
112500success2025-10-01
223200success2025-10-05
331150failed2025-10-08
4451000success2025-10-31
556300success2025-09-30
661700success2025-11-01
714250success2025-10-20

Output

user_iduser_type
2both
4both
1sender_only
3receiver_only
5receiver_only

How to solve PhonePe: Users Who Only Send, Only Receive or Do Both in October

The three groups are exactly the regions of a Venn diagram of two sets, so set operators say it directly. From the successful October transfers, take the set of senders (from_user) and the set of receivers (to_user). senders INTERSECT receivers is the two-way users, senders EXCEPT receivers those who only sent, and receivers EXCEPT senders those who only received. Each operator works on distinct rows, so a user who sent ten transfers still appears once. The three results are disjoint, so UNION ALL stacks them with their labels.

The filter must be applied to both sets the same way. In the example user 3's only outgoing transfer failed, so 3 is a receiver only; user 6 received in September and sent in November, so 6 is in neither set and does not appear; users 4 and 5 are on the October boundaries and both count.

The alternatives reach the same Venn regions without set operators: a UNION of everyone with two EXISTS tests per user, or a UNION ALL of flagged rows grouped by user with MAX of each flag. INTERSECT and EXCEPT need MySQL 8.0.31 or later; on older servers those forms are the way. All are linear in the transfers with a hash per set.

Reference solution (MySQL)

WITH senders AS (
  SELECT from_user AS user_id FROM P2pTransfer
  WHERE status = 'success' AND sent_on BETWEEN '2025-10-01' AND '2025-10-31'
), receivers AS (
  SELECT to_user AS user_id FROM P2pTransfer
  WHERE status = 'success' AND sent_on BETWEEN '2025-10-01' AND '2025-10-31'
)
SELECT user_id, 'both' AS user_type FROM (SELECT user_id FROM senders INTERSECT SELECT user_id FROM receivers) b
UNION ALL
SELECT user_id, 'sender_only' FROM (SELECT user_id FROM senders EXCEPT SELECT user_id FROM receivers) s
UNION ALL
SELECT user_id, 'receiver_only' FROM (SELECT user_id FROM receivers EXCEPT SELECT user_id FROM senders) r

Another way

WITH oct AS (SELECT from_user, to_user FROM P2pTransfer WHERE status = 'success' AND sent_on >= '2025-10-01' AND sent_on < '2025-11-01'),
everyone AS (SELECT from_user AS user_id FROM oct UNION SELECT to_user FROM oct)
SELECT e.user_id,
       CASE WHEN EXISTS (SELECT 1 FROM oct o WHERE o.from_user = e.user_id) AND EXISTS (SELECT 1 FROM oct o WHERE o.to_user = e.user_id) THEN 'both'
            WHEN EXISTS (SELECT 1 FROM oct o WHERE o.from_user = e.user_id) THEN 'sender_only'
            ELSE 'receiver_only' END AS user_type
FROM everyone e

Another way

SELECT user_id, CASE WHEN MAX(sent) = 1 AND MAX(got) = 1 THEN 'both' WHEN MAX(sent) = 1 THEN 'sender_only' ELSE 'receiver_only' END AS user_type
FROM (
  SELECT from_user AS user_id, 1 AS sent, 0 AS got FROM P2pTransfer WHERE status = 'success' AND YEAR(sent_on) = 2025 AND MONTH(sent_on) = 10
  UNION ALL
  SELECT to_user, 0, 1 FROM P2pTransfer WHERE status = 'success' AND YEAR(sent_on) = 2025 AND MONTH(sent_on) = 10
) x
GROUP BY user_id

← PhonePe: New Users Who Tried Three Products in Their First 30 Days · PhonePe: Rewards Campaign Redemption Report on 1 April →