PhonePe: Monthly P2P Versus P2M Payment Value in 2024

Medium
882.

PhonePe: Monthly P2P Versus P2M Payment Value in 2024

Strategy tracks how much of the money moving through the app goes person-to-person (P2P) and how much is paid to merchants (P2M). They want one row per month of 2024 to chart the two side by side.

For each month of 2024 with at least one successful payment, return month as 'YYYY-MM', p2p_value and p2m_value (the summed amount of that month's successful payments of each type, 0 when there were none of a type), and p2m_count, the number of successful P2M payments. Order the rows by month.

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: Payment

ColumnTypeValues
payment_idint
txn_typeenumP2P, P2M
amountint
statusenumsuccess, failed, pending
paid_atdatetime

Primary key: payment_id.

One row per payment; amount is in rupees.

Example

The rows must come back in the order shown.

Payment

payment_idtxn_typeamountstatuspaid_at
1P2P1500success2024-01-03 09:00:00
2P2M240success2024-01-03 13:10:00
3P2M999failed2024-01-09 18:00:00
4P2M60success2024-01-31 23:59:59
5P2P500success2023-12-31 23:00:00
6P2P2000success2024-03-15 10:00:00
7P2M120pending2024-03-16 11:00:00
8P2M75success2025-01-01 00:00:00

Output

monthp2p_valuep2m_valuep2m_count
2024-0115003002
2024-03200000
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: Monthly P2P Versus P2M Payment Value in 2024

Strategy tracks how much of the money moving through the app goes person-to-person (P2P) and how much is paid to merchants (P2M).

  • Difficulty: Medium
  • Topics: Conditional Logic, Dates, Aggregation
  • Dialect: MySQL
  • Problem: #882

Problem statement

Strategy tracks how much of the money moving through the app goes person-to-person (P2P) and how much is paid to merchants (P2M). They want one row per month of 2024 to chart the two side by side.

For each month of 2024 with at least one successful payment, return month as 'YYYY-MM', p2p_value and p2m_value (the summed amount of that month's successful payments of each type, 0 when there were none of a type), and p2m_count, the number of successful P2M payments. Order the rows by month.

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: Payment

ColumnType
payment_idint
txn_typeenum(P2P, P2M)
amountint
statusenum(success, failed, pending)
paid_atdatetime

Primary key: payment_id.

One row per payment; amount is in rupees.

Examples

Example 1

Payment

payment_idtxn_typeamountstatuspaid_at
1P2P1500success2024-01-03 09:00:00
2P2M240success2024-01-03 13:10:00
3P2M999failed2024-01-09 18:00:00
4P2M60success2024-01-31 23:59:59
5P2P500success2023-12-31 23:00:00
6P2P2000success2024-03-15 10:00:00
7P2M120pending2024-03-16 11:00:00
8P2M75success2025-01-01 00:00:00

Output

monthp2p_valuep2m_valuep2m_count
2024-0115003002
2024-03200000

How to solve PhonePe: Monthly P2P Versus P2M Payment Value in 2024

The answer has one row per month and the two types as columns, which is conditional aggregation: group by the month, and inside each group add amount only where the type matches, using CASE … ELSE 0. The ELSE 0 is what makes a month with no P2M payments show 0 rather than NULL. Counting P2M payments is the same trick with THEN 1.

Failed and pending payments moved no money, so status = 'success' is a row filter before grouping. The year is a half-open range on the datetime — >= '2024-01-01' and < '2025-01-01' — so 23:59:59 on New Year's Eve is in and midnight of 2025 is out. Months with only failed payments form no group, as asked. DATE_FORMAT(paid_at, '%Y-%m') builds the label, and grouping by that alias is valid in MySQL.

The first alternative uses LEFT(paid_at, 7) and IF/COUNT(IF(…, 1, NULL)); the second splits the types with two left self-joins, which is longer but shows what the CASE is doing. All are a single scan plus a tiny aggregate.

Reference solution (MySQL)

SELECT DATE_FORMAT(paid_at, '%Y-%m') AS month,
       SUM(CASE WHEN txn_type = 'P2P' THEN amount ELSE 0 END) AS p2p_value,
       SUM(CASE WHEN txn_type = 'P2M' THEN amount ELSE 0 END) AS p2m_value,
       SUM(CASE WHEN txn_type = 'P2M' THEN 1 ELSE 0 END) AS p2m_count
FROM Payment
WHERE status = 'success'
  AND paid_at >= '2024-01-01' AND paid_at < '2025-01-01'
GROUP BY month
ORDER BY month

Another way

SELECT LEFT(paid_at, 7) AS month, SUM(IF(txn_type = 'P2P', amount, 0)) AS p2p_value, SUM(IF(txn_type = 'P2M', amount, 0)) AS p2m_value, COUNT(IF(txn_type = 'P2M', 1, NULL)) AS p2m_count FROM Payment WHERE status = 'success' AND YEAR(paid_at) = 2024 GROUP BY LEFT(paid_at, 7) ORDER BY month

Another way

SELECT m.month, COALESCE(SUM(p2p.amount), 0) AS p2p_value, COALESCE(SUM(p2m.amount), 0) AS p2m_value, COUNT(p2m.payment_id) AS p2m_count
FROM (SELECT payment_id, DATE_FORMAT(paid_at, '%Y-%m') AS month FROM Payment WHERE status = 'success' AND YEAR(paid_at) = 2024) m
LEFT JOIN Payment p2p ON p2p.payment_id = m.payment_id AND p2p.txn_type = 'P2P'
LEFT JOIN Payment p2m ON p2m.payment_id = m.payment_id AND p2m.txn_type = 'P2M'
GROUP BY m.month ORDER BY m.month

← PhonePe: Most Frequent UPI Decline Code at Each Remitter Bank · PhonePe: Top District by Transaction Count in Each State, Q2 2025 →