Paytm BI Engineer Interview Questions and Answers 2026

WITH recent_txns AS (
    SELECT
        txn_id,
        user_id,
        LEFT(bank_ifsc, 4)   AS bank_prefix,
        status,
        failure_reason,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY user_id, LEFT(bank_ifsc,4)
                           ORDER BY created_at)           AS rn_all,
        ROW_NUMBER() OVER (PARTITION BY user_id, LEFT(bank_ifsc,4),
                           CASE WHEN status='failed' THEN 1 ELSE 0 END
                           ORDER BY created_at)           AS rn_status
    FROM upi_transactions
    WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
),
failure_groups AS (
    SELECT
        txn_id, user_id, bank_prefix, status, failure_reason, created_at,
        CASE WHEN status = 'failed'
             THEN rn_all - rn_status END           AS grp_id
    FROM recent_txns
    WHERE status = 'failed'
),
streaks AS (
    SELECT
        user_id, bank_prefix,
        grp_id,
        COUNT(*) AS streak_len
    FROM failure_groups
    GROUP BY user_id, bank_prefix, grp_id
),
max_streaks AS (
    SELECT bank_prefix, MAX(streak_len) AS max_streak
    FROM streaks
    GROUP BY bank_prefix
),
failure_summary AS (
    SELECT
        LEFT(bank_ifsc, 4)   AS bank_prefix,
        failure_reason,
        COUNT(*)                                                  AS total_failures,
        COUNT(*) * 1.0 / NULLIF(SUM(COUNT(*)) OVER
            (PARTITION BY LEFT(bank_ifsc,4)), 0) * 100           AS failure_rate_pct
    FROM upi_transactions
    WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY LEFT(bank_ifsc, 4), failure_reason
)
SELECT
    fs.bank_prefix,
    fs.failure_reason,
    fs.total_failures,
    ROUND(fs.failure_rate_pct, 1)  AS failure_rate_pct,
    ms.max_streak                  AS max_consecutive_failures
FROM failure_summary fs
LEFT JOIN max_streaks ms USING (bank_prefix)
ORDER BY fs.total_failures DESC;

WITH signed_txns AS (
    SELECT
        txn_id,
        user_id,
        created_at,
        txn_type,
        amount,
        CASE WHEN txn_type = 'credit'  THEN  amount
             WHEN txn_type = 'debit'   THEN -amount
        END  AS signed_amount
    FROM wallet_transactions
),
running AS (
    SELECT
        txn_id,
        user_id,
        txn_type,
        amount,
        created_at,
        SUM(signed_amount) OVER (
            PARTITION BY user_id
            ORDER BY created_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )  AS running_balance
    FROM signed_txns
),
first_negative AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
    FROM running
    WHERE running_balance < 0
)
SELECT
    user_id,
    txn_id                           AS first_negative_txn,
    txn_type,
    amount,
    created_at,
    running_balance                  AS balance_after_txn
FROM first_negative
WHERE rn = 1
ORDER BY created_at;

WITH monthly_gmv AS (
    SELECT
        merchant_id,
        category,
        DATE_TRUNC('month', txn_date)  AS month,
        SUM(gmv)                       AS total_gmv
    FROM merchant_transactions
    WHERE status = 'success'
      AND txn_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months')
    GROUP BY merchant_id, category, DATE_TRUNC('month', txn_date)
),
ranked AS (
    SELECT
        merchant_id,
        category,
        month,
        total_gmv,
        RANK()         OVER (PARTITION BY category, month ORDER BY total_gmv DESC)  AS gmv_rank,
        PERCENT_RANK() OVER (PARTITION BY category, month ORDER BY total_gmv)       AS gmv_percentile
    FROM monthly_gmv
),
curr  AS (SELECT * FROM ranked WHERE month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')),
prev  AS (SELECT * FROM ranked WHERE month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months'))
SELECT
    c.merchant_id,
    c.category,
    c.total_gmv,
    c.gmv_rank,
    ROUND(c.gmv_percentile * 100, 1)            AS gmv_percentile_pct,
    CASE
        WHEN p.gmv_percentile >= 0.75
         AND c.gmv_percentile < 0.50 THEN 'DROPPED_FROM_TOP_QUARTILE'
        ELSE 'STABLE'
    END  AS status_flag
FROM curr c
LEFT JOIN prev p USING (merchant_id, category)
ORDER BY c.category, c.gmv_rank;