Razorpay: Webhook Events That Never Got a 2xx Response

Medium
908.

Razorpay: Webhook Events That Never Got a 2xx Response

Razorpay retries a webhook until the merchant's server answers with a 2xx status. The developer-experience team wants the events that were attempted but never acknowledged, to email the merchants whose integration is broken.

A delivery attempt is acknowledged when http_status is between 200 and 299; a NULL status is a timeout. Return every event with at least one attempt and no acknowledged attempt: event_id, event_type, attempts (its number of attempts) and last_attempt_at (its latest attempt). Events never attempted are not reported. Any order.

A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.

Tables

Table: WebhookEvent

ColumnType
event_idint
merchant_idint
event_typevarchar
created_atdatetime

Primary key: event_id.

One row per event Razorpay raised for a merchant, such as payment.captured.

Table: DeliveryAttempt

ColumnType
attempt_idint
event_idint
attempted_atdatetime
http_statusint

Primary key: attempt_id.

One row per POST to the merchant's webhook URL; http_status is NULL when the request timed out.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

WebhookEvent

event_idmerchant_idevent_typecreated_at
1401payment.captured2025-03-10 10:00:00
2401refund.processed2025-03-10 10:05:00
3402settlement.processed2025-03-10 11:00:00
4402payment.failed2025-03-10 11:30:00
5403subscription.charged2025-03-10 12:00:00

DeliveryAttempt

attempt_idevent_idattempted_athttp_status
112025-03-10 10:00:02500
212025-03-10 10:05:02200
322025-03-10 10:05:03502
422025-03-10 10:10:03NULL
522025-03-10 10:40:03404
632025-03-10 11:00:04300
752025-03-10 12:00:01204

Output

event_idevent_typeattemptslast_attempt_at
2refund.processed32025-03-10 10:40:03
3settlement.processed12025-03-10 11:00:04
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.

Razorpay: Webhook Events That Never Got a 2xx Response

Razorpay retries a webhook until the merchant's server answers with a 2xx status.

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Conditional Logic
  • Dialect: MySQL
  • Problem: #908

Problem statement

Razorpay retries a webhook until the merchant's server answers with a 2xx status. The developer-experience team wants the events that were attempted but never acknowledged, to email the merchants whose integration is broken.

A delivery attempt is acknowledged when http_status is between 200 and 299; a NULL status is a timeout. Return every event with at least one attempt and no acknowledged attempt: event_id, event_type, attempts (its number of attempts) and last_attempt_at (its latest attempt). Events never attempted are not reported. Any order.

A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.

Tables

Table: WebhookEvent

ColumnType
event_idint
merchant_idint
event_typevarchar
created_atdatetime

Primary key: event_id.

One row per event Razorpay raised for a merchant, such as payment.captured.

Table: DeliveryAttempt

ColumnType
attempt_idint
event_idint
attempted_atdatetime
http_statusint

Primary key: attempt_id.

One row per POST to the merchant's webhook URL; http_status is NULL when the request timed out.

Examples

Example 1

WebhookEvent

event_idmerchant_idevent_typecreated_at
1401payment.captured2025-03-10 10:00:00
2401refund.processed2025-03-10 10:05:00
3402settlement.processed2025-03-10 11:00:00
4402payment.failed2025-03-10 11:30:00
5403subscription.charged2025-03-10 12:00:00

DeliveryAttempt

attempt_idevent_idattempted_athttp_status
112025-03-10 10:00:02500
212025-03-10 10:05:02200
322025-03-10 10:05:03502
422025-03-10 10:10:03NULL
522025-03-10 10:40:03404
632025-03-10 11:00:04300
752025-03-10 12:00:01204

Output

event_idevent_typeattemptslast_attempt_at
2refund.processed32025-03-10 10:40:03
3settlement.processed12025-03-10 11:00:04

How to solve Razorpay: Webhook Events That Never Got a 2xx Response

The rule is about all of an event's attempts at once — attempted, and never acknowledged — so group the attempts per event. The inner join with DeliveryAttempt already removes events with no attempts, and COUNT(*) and MAX(attempted_at) give the two reported numbers.

"Never acknowledged" becomes HAVING SUM(CASE WHEN http_status BETWEEN 200 AND 299 THEN 1 ELSE 0 END) = 0. Both ends of BETWEEN are inclusive, so 200 and 299 succeed while 199 and 300 do not. A timeout has http_status NULL; NULL BETWEEN … is unknown, so the CASE falls to ELSE 0 and the timeout counts as a failed attempt, as it should. Filtering failures in WHERE instead would be wrong — it would report an event that failed once and succeeded on the retry.

The alternative states the rule with EXISTS / NOT EXISTS and fetches the count and the latest time with scalar subqueries. With an index on DeliveryAttempt(event_id) both read each attempt once.

Reference solution (MySQL)

SELECT e.event_id, e.event_type, COUNT(*) AS attempts, MAX(a.attempted_at) AS last_attempt_at
FROM WebhookEvent e
JOIN DeliveryAttempt a ON a.event_id = e.event_id
GROUP BY e.event_id, e.event_type
HAVING SUM(CASE WHEN a.http_status BETWEEN 200 AND 299 THEN 1 ELSE 0 END) = 0

Another way

SELECT e.event_id, e.event_type,
       (SELECT COUNT(*) FROM DeliveryAttempt a WHERE a.event_id = e.event_id) AS attempts,
       (SELECT MAX(a.attempted_at) FROM DeliveryAttempt a WHERE a.event_id = e.event_id) AS last_attempt_at
FROM WebhookEvent e
WHERE EXISTS (SELECT 1 FROM DeliveryAttempt a WHERE a.event_id = e.event_id)
  AND NOT EXISTS (SELECT 1 FROM DeliveryAttempt a WHERE a.event_id = e.event_id AND a.http_status >= 200 AND a.http_status < 300)

← Razorpay: Chargeback Rate by Merchant Category · Razorpay: Subscription Cycles Recovered by Retry vs Halted →