Razorpay: Magic Checkout Conversion Rate by Merchant

Easy
905.

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.

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

ColumnTypeValues
session_idint
merchant_idint
started_atdatetime
outcomeenumorder_placed, abandoned, payment_failed

Primary key: session_id.

One row per time a shopper opened Magic Checkout on a merchant's store.

Example

The rows must come back in the order shown.

CheckoutSession

session_idmerchant_idstarted_atoutcome
13012025-03-01 10:02:00order_placed
23012025-03-01 10:15:00abandoned
33012025-03-01 11:40:00order_placed
43022025-03-01 12:00:00payment_failed
53022025-03-01 12:30:00order_placed
63032025-03-02 09:10:00abandoned
73042025-03-02 09:45:00order_placed
83042025-03-02 10:05:00abandoned

Output

merchant_idsessionsordersconversion_pct
3013266.67
3022150
3042150
303100
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: 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

ColumnType
session_idint
merchant_idint
started_atdatetime
outcomeenum(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_idmerchant_idstarted_atoutcome
13012025-03-01 10:02:00order_placed
23012025-03-01 10:15:00abandoned
33012025-03-01 11:40:00order_placed
43022025-03-01 12:00:00payment_failed
53022025-03-01 12:30:00order_placed
63032025-03-02 09:10:00abandoned
73042025-03-02 09:45:00order_placed
83042025-03-02 10:05:00abandoned

Output

merchant_idsessionsordersconversion_pct
3013266.67
3022150
3042150
303100

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_id

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