PhonePe Data Scientist Interview Questions and Answers 2026

WITH first_txn AS (
    SELECT
        user_id,
        MIN(created_at)  AS first_txn_ts
    FROM transactions
    WHERE status = 'success'
      AND DATE_TRUNC('month', created_at) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
    GROUP BY user_id
),
eligible_referrals AS (
    SELECT
        r.referral_id,
        r.referrer_user_id,
        r.referred_user_id,
        r.referral_ts,
        f.first_txn_ts,
        ROW_NUMBER() OVER (PARTITION BY r.referred_user_id
                           ORDER BY r.referral_ts ASC)  AS first_touch_rank,
        ROW_NUMBER() OVER (PARTITION BY r.referred_user_id
                           ORDER BY r.referral_ts DESC) AS last_touch_rank
    FROM referrals r
    JOIN first_txn f
        ON f.user_id = r.referred_user_id
       AND r.referral_ts < f.first_txn_ts   -- referral must precede first txn
),
first_touch AS (
    SELECT referrer_user_id, COUNT(*) * 100 AS first_touch_cashback
    FROM eligible_referrals
    WHERE first_touch_rank = 1
    GROUP BY referrer_user_id
),
last_touch AS (
    SELECT referrer_user_id, COUNT(*) * 100 AS last_touch_cashback
    FROM eligible_referrals
    WHERE last_touch_rank = 1
    GROUP BY referrer_user_id
)
SELECT
    COALESCE(ft.referrer_user_id, lt.referrer_user_id)  AS referrer_user_id,
    COALESCE(ft.first_touch_cashback, 0)                AS first_touch_cashback,
    COALESCE(lt.last_touch_cashback, 0)                 AS last_touch_cashback,
    COALESCE(ft.first_touch_cashback, 0)
        - COALESCE(lt.last_touch_cashback, 0)           AS cashback_diff
FROM first_touch ft
FULL OUTER JOIN last_touch lt USING (referrer_user_id)
ORDER BY ABS(cashback_diff) DESC;

WITH monthly AS (
    SELECT
        city,
        category,
        DATE_TRUNC('month', created_at)                               AS month,
        COUNT(*)                                                       AS total_attempts,
        SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END)           AS successes,
        SUM(CASE WHEN failure_reason = 'bank_timeout' THEN 1 ELSE 0 END) AS timeout_failures
    FROM merchant_payments
    WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months')
    GROUP BY city, category, DATE_TRUNC('month', created_at)
),
curr AS (SELECT * FROM monthly WHERE month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')),
prev AS (SELECT * FROM monthly WHERE month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months'))
SELECT
    c.city,
    c.category,
    ROUND(c.successes * 100.0 / NULLIF(c.total_attempts, 0), 1)    AS curr_success_rate,
    ROUND(p.successes * 100.0 / NULLIF(p.total_attempts, 0), 1)    AS prev_success_rate,
    ROUND(c.successes * 100.0 / NULLIF(c.total_attempts, 0)
        - p.successes * 100.0 / NULLIF(p.total_attempts, 0), 1)    AS rate_change_pp,
    ROUND(c.timeout_failures * 100.0 / NULLIF(c.total_attempts-c.successes, 0), 1)
                                                                    AS timeout_share_of_failures,
    CASE
        WHEN c.timeout_failures * 1.0 / NULLIF(c.total_attempts-c.successes, 0) > 0.5
        THEN 'BANK_TIMEOUT_DRIVEN' ELSE 'OTHER_FAILURES'
    END  AS failure_driver
FROM curr c
JOIN prev p USING (city, category)
WHERE c.successes * 100.0 / NULLIF(c.total_attempts, 0)
    - p.successes * 100.0 / NULLIF(p.total_attempts, 0) < -5
ORDER BY rate_change_pp;

import numpy as np
import pandas as pd
from scipy import stats

# Check distribution shape
ctrl = df[df['group']=='control']['txn_amount']
trt  = df[df['group']=='treatment']['txn_amount']

print("Skewness — Control:", ctrl.skew(), "Treatment:", trt.skew())
print("Kurtosis — Control:", ctrl.kurtosis(), "Treatment:", trt.kurtosis())

# Problem: highly skewed → t-test unreliable even at n=5000

# Option 1: Mann-Whitney U (compares distributions, not means)
stat, p_mw = stats.mannwhitneyu(trt, ctrl, alternative='two-sided')
print(f"\nMann-Whitney p-value: {p_mw:.4f}")
# Interprets: P(trt amount > ctrl amount) vs 0.5

# Option 2: Bootstrap CI on mean difference
n_boot = 10000
boot_diffs = []
for _ in range(n_boot):
    b_ctrl = ctrl.sample(len(ctrl), replace=True).mean()
    b_trt  = trt.sample(len(trt),  replace=True).mean()
    boot_diffs.append(b_trt - b_ctrl)

boot_diffs = np.array(boot_diffs)
ci_lo, ci_hi = np.percentile(boot_diffs, [2.5, 97.5])
print(f"\nBootstrap 95% CI on mean diff: [{ci_lo:.2f}, {ci_hi:.2f}]")
print("Significant?" , "YES" if ci_lo > 0 or ci_hi < 0 else "NO")

# Option 3: Log-transform if ratio is more meaningful than absolute diff
ctrl_log = np.log1p(ctrl)
trt_log  = np.log1p(trt)
t_stat, p_log = stats.ttest_ind(trt_log, ctrl_log)
print(f"\nt-test on log(amount): p={p_log:.4f}")

from statsmodels.stats.power import TTestIndPower

analysis = TTestIndPower()

# Parameters
baseline_mean = 2.3          # avg transactions/user/day in control
std_dev       = 4.1          # std of daily transactions/user
mde           = 0.115        # 5% lift = 0.05 * 2.3
effect_size   = mde / std_dev  # Cohen's d

# Sample size per group
n_per_group = analysis.solve_power(
    effect_size = effect_size,
    alpha       = 0.05,
    power       = 0.80,
    alternative = 'two-sided'
)
print(f"Cohen's d: {effect_size:.4f}")
print(f"Users needed per group: {int(n_per_group)+1:,}")
print(f"Total users: {(int(n_per_group)+1)*2:,}")

# Business interpretation
daily_active_users = 50_000_000  # PhonePe's ~50M DAU
exposure_pct = (n_per_group * 2) / daily_active_users
print(f"Exposure needed: {exposure_pct*100:.1f}% of DAU")

# If sample available → estimate runtime
# Assume 30% of DAU eligible for cashback experiment
eligible_per_day = daily_active_users * 0.30
days_needed = (n_per_group * 2) / eligible_per_day
print(f"Days needed at 30% eligibility: {days_needed:.1f} days")