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
| Column | Type |
|---|---|
| transfer_id | int |
| from_user | int |
| to_user | int |
| amount | int |
| status | enum(success, failed) |
| sent_on | date |
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_id | from_user | to_user | amount | status | sent_on |
|---|---|---|---|---|---|
| 1 | 1 | 2 | 500 | success | 2025-10-01 |
| 2 | 2 | 3 | 200 | success | 2025-10-05 |
| 3 | 3 | 1 | 150 | failed | 2025-10-08 |
| 4 | 4 | 5 | 1000 | success | 2025-10-31 |
| 5 | 5 | 6 | 300 | success | 2025-09-30 |
| 6 | 6 | 1 | 700 | success | 2025-11-01 |
| 7 | 1 | 4 | 250 | success | 2025-10-20 |
Output
| user_id | user_type |
|---|---|
| 2 | both |
| 4 | both |
| 1 | sender_only |
| 3 | receiver_only |
| 5 | receiver_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) rAnother 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 eAnother 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 →