UPI Success Rate by App and Month — SQL Dates Problem
The payments team tracks how reliable each UPI app is month by month. A payment attempt either succeeds or fails.
- Difficulty: Medium
- Topics: Dates, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #56
Problem statement
The payments team tracks how reliable each UPI app is month by month. A payment attempt either succeeds or fails.
For every calendar month and UPI app that had at least one attempt, return month (as 'YYYY-MM'), upi_app, attempts (all attempts), successful (attempts with status SUCCESS) and success_rate_pct — 100 × successful ÷ attempts, rounded to 2 decimals. Order the rows by month, then by upi_app.
Tables
Table: UpiPayment
| Column | Type |
|---|---|
| payment_id | int |
| upi_app | enum(BHIM, Google Pay, Paytm, PhonePe) |
| status | enum(SUCCESS, FAILED) |
| amount | int |
| paid_at | datetime |
Primary key: payment_id.
One row per payment attempt; paid_at is when it was attempted.
Examples
Example 1
UpiPayment
| payment_id | upi_app | status | amount | paid_at |
|---|---|---|---|---|
| 1 | PhonePe | SUCCESS | 450 | 2024-05-02 09:15:00 |
| 2 | PhonePe | FAILED | 1200 | 2024-05-11 13:40:22 |
| 3 | PhonePe | SUCCESS | 80 | 2024-05-31 23:59:59 |
| 4 | Google Pay | SUCCESS | 2500 | 2024-05-20 18:05:10 |
| 5 | Paytm | FAILED | 640 | 2024-06-01 00:00:05 |
| 6 | PhonePe | SUCCESS | 99 | 2024-06-03 10:10:10 |
| 7 | Paytm | SUCCESS | 300 | 2024-06-15 12:00:00 |
| 8 | Paytm | SUCCESS | 150 | 2024-06-28 19:45:00 |
Output
| month | upi_app | attempts | successful | success_rate_pct |
|---|---|---|---|---|
| 2024-05 | Google Pay | 1 | 1 | 100 |
| 2024-05 | PhonePe | 3 | 2 | 66.67 |
| 2024-06 | Paytm | 3 | 2 | 66.67 |
| 2024-06 | PhonePe | 1 | 1 | 100 |
How to solve UPI Success Rate by App and Month
Two ideas combine here: date bucketing and conditional aggregation. DATE_FORMAT(paid_at, '%Y-%m') turns every timestamp into its month label, which both groups and sorts correctly because the format is fixed-width. Grouping by that label and upi_app gives one row per app per month that actually had attempts — months with no attempts for an app simply have no group, as the statement wants.
Inside a group, COUNT(*) counts every attempt, and SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) counts only the successes. The rate is their ratio times 100, rounded to two decimals; rounding matters because MySQL's division keeps four decimals and other engines keep more. Note the edges: a payment at 23:59:59 on the last day stays in its month, and one at 00:00:05 on the 1st starts the next.
Alternatives: take LEFT(paid_at, 7) as the month and average a 0/1 flag (the average of a flag is the success share), or build the label from YEAR and MONTH. The query is one scan and a group-by.
Reference solution (MySQL)
SELECT DATE_FORMAT(paid_at, '%Y-%m') AS month,
upi_app,
COUNT(*) AS attempts,
SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS successful,
ROUND(100 * SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) / COUNT(*), 2) AS success_rate_pct
FROM UpiPayment
GROUP BY DATE_FORMAT(paid_at, '%Y-%m'), upi_app
ORDER BY month, upi_appAnother way
SELECT month, upi_app, COUNT(*) AS attempts, SUM(ok) AS successful, ROUND(AVG(ok) * 100, 2) AS success_rate_pct
FROM (SELECT LEFT(paid_at, 7) AS month, upi_app, IF(status = 'SUCCESS', 1, 0) AS ok FROM UpiPayment) t
GROUP BY month, upi_app ORDER BY month, upi_appAnother way
SELECT CONCAT(YEAR(paid_at), '-', LPAD(MONTH(paid_at), 2, '0')) AS month, upi_app, COUNT(*) AS attempts,
COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) AS successful,
ROUND(COUNT(CASE WHEN status = 'SUCCESS' THEN 1 END) * 100 / COUNT(*), 2) AS success_rate_pct
FROM UpiPayment GROUP BY month, upi_app ORDER BY month, upi_app← Savings Accounts Above the Bank-Wide Average Balance · Current Balance From the Account Ledger →