Money moved by payment type
mediumMoney moved by payment type
PhonePe SQL Interview Question
PhonePe makes money on merchant payments, breaks even on bill payments and loses a little on transfers between people. A user who sends money to friends all day is a cost; one who pays merchants is a profit. Telling them apart means knowing how much each user moved of each kind, and a failed transaction moved nothing.
Return the user_id, and the money that went through successfully for each kind as p2p_amount, merchant_amount and bill_amount. A kind a user never used counts as zero.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
phonepe_transactionsTable30 rows
| Column Name | Type |
|---|---|
| txn_id | BIGINT |
| user_id | VARCHAR |
| txn_at | TIMESTAMP |
| txn_type | VARCHAR |
| amount | DOUBLE |
| status | VARCHAR |
phonepe_transactionsExample Input
| txn_id | user_id | txn_at | txn_type | amount | status |
|---|---|---|---|---|---|
| 8001 | U01 | 2024-09-02 09:14:00 | p2p | 500 | success |
| 8002 | U01 | 2024-09-02 09:40:00 | merchant | 1250 | failed |
| 8003 | U01 | 2024-09-02 10:05:00 | merchant | 1250 | failed |
| 8004 | U01 | 2024-09-02 10:31:00 | merchant | 1250 | success |
| 8005 | U01 | 2024-09-02 13:22:00 | bill | 2300 | success |
| 8006 | U01 | 2024-09-03 08:10:00 | p2p | 750 | failed |
| 8007 | U01 | 2024-09-03 11:48:00 | p2p | 750 | success |
| 8027 | U05 | 2024-09-02 06:40:00 | merchant | 150 | success |
| 8028 | U05 | 2024-09-02 18:25:00 | p2p | 2200 | success |
| 8029 | U05 | 2024-09-03 10:55:00 | bill | 1050 | failed |
| 8030 | U05 | 2024-09-03 11:21:00 | bill | 1050 | success |
Example Output
| user_id | p2p_amount | merchant_amount | bill_amount |
|---|---|---|---|
| U01 | 1250 | 1250 | 2300 |
| U05 | 2200 | 150 | 1050 |
Explanation
U01 moved 1250 in transfers, 1250 to merchants and 2300 in bills. The merchant figure counts only the attempt that succeeded, not the two that failed ahead of it, because a failed attempt moved no money.
The example above is a small slice of the data. Your query runs against the full tables.
Company
PhonePe
Difficulty
medium
Topic
reshaping
Language
SQL
Your query
Starting the SQL engine...