Razorpay Senior Business Analyst Interview Questions and Answers 2026

SELECT
    m.category,
    DATE_TRUNC('month', t.created_at)::DATE    AS month,
    COUNT(*)                                    AS total_txns,
    COUNT(CASE WHEN t.status = 'success' THEN 1 END) AS success_txns,
    ROUND(
        100.0 * COUNT(CASE WHEN t.status = 'success' THEN 1 END)
              / NULLIF(COUNT(*), 0), 2
    ) AS success_rate_pct
FROM transactions t
JOIN merchants m ON t.merchant_id = m.merchant_id
WHERE t.created_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY m.category, DATE_TRUNC('month', t.created_at)
ORDER BY m.category, month;

WITH monthly_gmv AS (
    SELECT
        merchant_id,
        DATE_TRUNC('month', created_at)::DATE AS month,
        SUM(amount) AS gmv
    FROM transactions
    WHERE status = 'success'
    GROUP BY merchant_id, DATE_TRUNC('month', created_at)
),
gmv_with_lag AS (
    SELECT
        merchant_id,
        month,
        gmv,
        LAG(gmv) OVER (PARTITION BY merchant_id ORDER BY month) AS prev_gmv
    FROM monthly_gmv
)
SELECT
    merchant_id,
    month,
    gmv,
    prev_gmv,
    ROUND(
        100.0 * (gmv - prev_gmv) / NULLIF(prev_gmv, 0), 1
    ) AS mom_change_pct,
    CASE
        WHEN (gmv - prev_gmv) / NULLIF(prev_gmv, 0) < -0.20 THEN 'ALERT: >20% Drop'
        ELSE 'Normal'
    END AS alert_flag
FROM gmv_with_lag
WHERE prev_gmv IS NOT NULL    -- exclude first month
ORDER BY merchant_id, month;

WITH merchant_stats AS (
    SELECT
        t.merchant_id,
        SUM(t.amount)           AS total_txn_amount,
        COUNT(DISTINCT t.txn_id) AS total_txns
    FROM transactions t
    WHERE t.created_at >= CURRENT_DATE - INTERVAL '30 days'
      AND t.status = 'success'
    GROUP BY t.merchant_id
),
refund_stats AS (
    SELECT
        t.merchant_id,
        SUM(r.refund_amount)               AS total_refund_amount,
        COUNT(DISTINCT t.customer_id)       AS refunded_customers
    FROM refunds r
    JOIN transactions t ON r.txn_id = t.txn_id
    WHERE r.refund_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY t.merchant_id
)
SELECT
    ms.merchant_id,
    ms.total_txn_amount,
    COALESCE(rs.total_refund_amount, 0)  AS total_refund_amount,
    ROUND(
        100.0 * COALESCE(rs.total_refund_amount, 0) / NULLIF(ms.total_txn_amount, 0), 2
    )                                     AS refund_rate_pct,
    COALESCE(rs.refunded_customers, 0)   AS refunded_customers
FROM merchant_stats ms
LEFT JOIN refund_stats rs ON ms.merchant_id = rs.merchant_id
WHERE COALESCE(rs.total_refund_amount, 0) / NULLIF(ms.total_txn_amount, 0) > 0.15
ORDER BY refund_rate_pct DESC;

WITH first_txn AS (
    SELECT
        merchant_id,
        DATE_TRUNC('month', MIN(created_at))::DATE AS cohort_month
    FROM transactions
    WHERE status = 'success'
    GROUP BY merchant_id
),
monthly_activity AS (
    SELECT
        t.merchant_id,
        DATE_TRUNC('month', t.created_at)::DATE AS activity_month
    FROM transactions t
    WHERE t.status = 'success'
    GROUP BY t.merchant_id, DATE_TRUNC('month', t.created_at)
),
cohort_activity AS (
    SELECT
        f.cohort_month,
        EXTRACT(YEAR FROM AGE(m.activity_month, f.cohort_month)) * 12
          + EXTRACT(MONTH FROM AGE(m.activity_month, f.cohort_month)) AS months_since_start,
        COUNT(DISTINCT m.merchant_id) AS active_merchants
    FROM first_txn f
    JOIN monthly_activity m ON f.merchant_id = m.merchant_id
    WHERE EXTRACT(YEAR FROM AGE(m.activity_month, f.cohort_month)) * 12
          + EXTRACT(MONTH FROM AGE(m.activity_month, f.cohort_month)) BETWEEN 0 AND 3
    GROUP BY f.cohort_month, months_since_start
),
cohort_size AS (
    SELECT cohort_month, COUNT(*) AS total_merchants
    FROM first_txn
    GROUP BY cohort_month
)
SELECT
    ca.cohort_month,
    cs.total_merchants,
    ca.months_since_start,
    ca.active_merchants,
    ROUND(100.0 * ca.active_merchants / cs.total_merchants, 1) AS retention_pct
FROM cohort_activity ca
JOIN cohort_size cs ON ca.cohort_month = cs.cohort_month
ORDER BY ca.cohort_month, ca.months_since_start;

SELECT
    m.tier,
    COUNT(DISTINCT t.txn_id)                              AS settled_txns,
    ROUND(AVG(
        EXTRACT(EPOCH FROM (s.settlement_date::timestamp
                            - t.created_at::timestamp)) / 86400
    ), 2)                                                 AS avg_settlement_days,
    CASE
        WHEN AVG(
            EXTRACT(EPOCH FROM (s.settlement_date::timestamp
                                - t.created_at::timestamp)) / 86400
        ) > 3 THEN 'BREACH: >3 days'
        ELSE 'Within SLA'
    END AS sla_status
FROM transactions t
JOIN settlements s ON t.txn_id = s.txn_id
JOIN merchants m ON t.merchant_id = m.merchant_id
WHERE t.status = 'success'
GROUP BY m.tier
ORDER BY avg_settlement_days DESC;

import pandas as pd

def merchant_churn_by_cohort(df: pd.DataFrame, churn_window_days: int = 30) -> pd.DataFrame:
    """
    df columns: merchant_id, txn_date (datetime), amount
    Returns: cohort_week, total_merchants, churned_merchants, churn_rate_pct
    """
    df = df.copy()
    df['txn_date'] = pd.to_datetime(df['txn_date'])

    # Cohort = week of first transaction
    df['first_txn'] = df.groupby('merchant_id')['txn_date'].transform('min')
    df['cohort_week'] = df['first_txn'].dt.to_period('W').dt.start_time

    # Last transaction date per merchant
    last_txn = df.groupby('merchant_id')['txn_date'].max().reset_index()
    last_txn.columns = ['merchant_id', 'last_txn_date']

    # Cohort per merchant
    cohort_map = df[['merchant_id', 'cohort_week']].drop_duplicates()
    merged = cohort_map.merge(last_txn, on='merchant_id')

    today = df['txn_date'].max()
    merged['is_churned'] = (today - merged['last_txn_date']).dt.days > churn_window_days

    result = (
        merged
        .groupby('cohort_week')
        .agg(
            total_merchants=('merchant_id', 'count'),
            churned_merchants=('is_churned', 'sum')
        )
        .reset_index()
    )
    result['churn_rate_pct'] = round(
        100 * result['churned_merchants'] / result['total_merchants'], 2
    )
    return result

import pandas as pd
import numpy as np
from statsmodels.stats.proportion import proportions_ztest

def test_conversion_lift(df: pd.DataFrame) -> dict:
    """
    df columns: user_id, variant ('control'/'treatment'), converted (0/1)
    """
    ctrl  = df[df['variant'] == 'control']
    treat = df[df['variant'] == 'treatment']

    n_ctrl,  conv_ctrl  = len(ctrl),  ctrl['converted'].sum()
    n_treat, conv_treat = len(treat), treat['converted'].sum()

    rate_ctrl  = conv_ctrl  / n_ctrl
    rate_treat = conv_treat / n_treat
    lift_pct   = (rate_treat - rate_ctrl) / rate_ctrl * 100

    # Two-proportion z-test (one-sided: is treatment > control?)
    count = np.array([conv_treat, conv_ctrl])
    nobs  = np.array([n_treat,    n_ctrl])
    z_stat, p_value = proportions_ztest(count, nobs, alternative='larger')

    return {
        'control_rate':    round(rate_ctrl,  4),
        'treatment_rate':  round(rate_treat, 4),
        'lift_pct':        round(lift_pct,   2),
        'z_statistic':     round(z_stat,     3),
        'p_value':         round(p_value,    4),
        'significant_95':  p_value < 0.05,
        'recommendation':  'Ship' if (p_value < 0.05 and lift_pct > 0) else 'Do not ship'
    }

# Usage
# result = test_conversion_lift(df)
# print(result)

import pandas as pd
import json

def analyze_webhook_failures(raw_logs: list[str]) -> pd.DataFrame:
    """
    raw_logs: list of JSON strings
    Returns: top 5 error codes with count and affected merchant list
    """
    records = []
    for line in raw_logs:
        try:
            log = json.loads(line)
            if log.get('status') == 'failed' and log.get('error'):
                records.append({
                    'merchant_id': log['merchant_id'],
                    'event_type':  log['event_type'],
                    'error_code':  log['error'].get('code', 'UNKNOWN'),
                    'error_desc':  log['error'].get('description', ''),
                })
        except (json.JSONDecodeError, KeyError):
            continue   # skip malformed logs gracefully

    df = pd.DataFrame(records)
    if df.empty:
        return df

    summary = (
        df.groupby('error_code')
        .agg(
            failure_count   = ('merchant_id', 'count'),
            unique_merchants = ('merchant_id', 'nunique'),
            top_merchants   = ('merchant_id',
                               lambda x: ', '.join(x.value_counts().head(3).index.tolist())),
            description     = ('error_desc', 'first')
        )
        .reset_index()
        .sort_values('failure_count', ascending=False)
        .head(5)
    )
    return summary