UPI Success Rate by App and Month

Medium
56.

UPI Success Rate by App and Month

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

ColumnTypeValues
payment_idint
upi_appenumBHIM, Google Pay, Paytm, PhonePe
statusenumSUCCESS, FAILED
amountint
paid_atdatetime

Primary key: payment_id.

One row per payment attempt; paid_at is when it was attempted.

Example

The rows must come back in the order shown.

UpiPayment

payment_idupi_appstatusamountpaid_at
1PhonePeSUCCESS4502024-05-02 09:15:00
2PhonePeFAILED12002024-05-11 13:40:22
3PhonePeSUCCESS802024-05-31 23:59:59
4Google PaySUCCESS25002024-05-20 18:05:10
5PaytmFAILED6402024-06-01 00:00:05
6PhonePeSUCCESS992024-06-03 10:10:10
7PaytmSUCCESS3002024-06-15 12:00:00
8PaytmSUCCESS1502024-06-28 19:45:00

Output

monthupi_appattemptssuccessfulsuccess_rate_pct
2024-05Google Pay11100
2024-05PhonePe3266.67
2024-06Paytm3266.67
2024-06PhonePe11100
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.

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

ColumnType
payment_idint
upi_appenum(BHIM, Google Pay, Paytm, PhonePe)
statusenum(SUCCESS, FAILED)
amountint
paid_atdatetime

Primary key: payment_id.

One row per payment attempt; paid_at is when it was attempted.

Examples

Example 1

UpiPayment

payment_idupi_appstatusamountpaid_at
1PhonePeSUCCESS4502024-05-02 09:15:00
2PhonePeFAILED12002024-05-11 13:40:22
3PhonePeSUCCESS802024-05-31 23:59:59
4Google PaySUCCESS25002024-05-20 18:05:10
5PaytmFAILED6402024-06-01 00:00:05
6PhonePeSUCCESS992024-06-03 10:10:10
7PaytmSUCCESS3002024-06-15 12:00:00
8PaytmSUCCESS1502024-06-28 19:45:00

Output

monthupi_appattemptssuccessfulsuccess_rate_pct
2024-05Google Pay11100
2024-05PhonePe3266.67
2024-06Paytm3266.67
2024-06PhonePe11100

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_app

Another 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_app

Another 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 →