Razorpay: Magic Checkout Conversion Rate by Merchant
The Magic Checkout product team at Razorpay compares how well the one-click checkout converts for each merchant that uses it.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #905
Problem statement
The Magic Checkout product team at Razorpay compares how well the one-click checkout converts for each merchant that uses it.
For every merchant with at least one checkout session, return merchant_id, sessions (all its sessions), orders (sessions whose outcome is order_placed) and conversion_pct = 100 × orders / sessions, rounded to 2 decimals. Order the rows by conversion_pct descending, then merchant_id ascending.
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: CheckoutSession
| Column | Type |
|---|---|
| session_id | int |
| merchant_id | int |
| started_at | datetime |
| outcome | enum(order_placed, abandoned, payment_failed) |
Primary key: session_id.
One row per time a shopper opened Magic Checkout on a merchant's store.
Examples
Example 1
CheckoutSession
| session_id | merchant_id | started_at | outcome |
|---|---|---|---|
| 1 | 301 | 2025-03-01 10:02:00 | order_placed |
| 2 | 301 | 2025-03-01 10:15:00 | abandoned |
| 3 | 301 | 2025-03-01 11:40:00 | order_placed |
| 4 | 302 | 2025-03-01 12:00:00 | payment_failed |
| 5 | 302 | 2025-03-01 12:30:00 | order_placed |
| 6 | 303 | 2025-03-02 09:10:00 | abandoned |
| 7 | 304 | 2025-03-02 09:45:00 | order_placed |
| 8 | 304 | 2025-03-02 10:05:00 | abandoned |
Output
| merchant_id | sessions | orders | conversion_pct |
|---|---|---|---|
| 301 | 3 | 2 | 66.67 |
| 302 | 2 | 1 | 50 |
| 304 | 2 | 1 | 50 |
| 303 | 1 | 0 | 0 |
How to solve Razorpay: Magic Checkout Conversion Rate by Merchant
Conversion is a ratio of two counts over the same group, so one GROUP BY merchant_id gives both: COUNT(*) is every session, and SUM(CASE WHEN outcome = 'order_placed' THEN 1 ELSE 0 END) counts only the sessions that ended in an order. Abandoned and payment-failed sessions add to the denominator only.
Multiply by 100 before dividing and round to 2 decimals at the end. MySQL's / is decimal division, so there is no integer-division trap, and rounding last avoids compounding errors. A merchant whose every session was abandoned still appears with orders = 0 and a rate of 0.
The statement fixes the order: highest conversion first, and merchant_id breaks ties between merchants with the same rate. The alternative computes the two counts in a derived table — using COUNT(IF(…, 1, NULL)), which counts only non-NULL values — and divides outside. Both are a single pass plus a sort of one row per merchant.
Reference solution (MySQL)
SELECT merchant_id,
COUNT(*) AS sessions,
SUM(CASE WHEN outcome = 'order_placed' THEN 1 ELSE 0 END) AS orders,
ROUND(100 * SUM(CASE WHEN outcome = 'order_placed' THEN 1 ELSE 0 END) / COUNT(*), 2) AS conversion_pct
FROM CheckoutSession
GROUP BY merchant_id
ORDER BY conversion_pct DESC, merchant_idAnother way
SELECT merchant_id, sessions, orders, ROUND(100 * orders / sessions, 2) AS conversion_pct
FROM (SELECT merchant_id, COUNT(*) AS sessions, COUNT(IF(outcome = 'order_placed', 1, NULL)) AS orders
FROM CheckoutSession GROUP BY merchant_id) t
ORDER BY 4 DESC, 1← Razorpay: Fund Accounts With a Malformed UPI ID · Razorpay: Card Payment Success Rate by Issuing Bank →