PhonePe: Decision Scientist

1. Validate: a logging or definition change? A partial-day effect? Compare with the same weekday last week.
2. Segment the drop: - Bank (issuer or remitter): one bank's server down? - PSP/app version, OS, merchant category, transaction type (P2P vs P2M), amount band, geography, hour.
3. Error codes: timeouts vs declines vs "insufficient funds" (user behaviour) vs technical failures.
4. External events: NPCI incidents, bank maintenance windows, festivals with traffic spikes.
5. Quantify each contributor (contribution to the delta), then recommend: route around a failing bank, alert the partner, add retries, fix the app bug.
6. Prevent: anomaly alerts by bank and error code.

users(user_id PK, name, phone UNIQUE, email, city, created_at)
theatres(theatre_id PK, name, city, address)
screens_seats(seat_id PK, theatre_id FK, screen_no, row_label, seat_no, seat_type)
shows(show_id PK, movie_id FK, theatre_id FK, screen_no, start_time, base_price)
bookings(booking_id PK, user_id FK, show_id FK, status, amount, payment_id, created_at)
booking_seats(booking_id FK, seat_id FK, show_id FK, PRIMARY KEY(show_id, seat_id))  -- prevents double booking

• Plus movies(movie_id, title, language, duration, rating).
• Explain the unique (show_id, seat_id) constraint and seat locking with a TTL during payment. Index shows(theatre_id, start_time).

WITH m AS (
  SELECT user_id, DATE_TRUNC('month', txn_time) AS mon, COUNT(*) AS n
  FROM txns GROUP BY 1, 2
), w AS (
  SELECT user_id, mon, n, LAG(n) OVER (PARTITION BY user_id ORDER BY mon) AS prev_n FROM m
)
SELECT user_id, mon, prev_n, n
FROM w WHERE prev_n IS NOT NULL AND n < 0.5 * prev_n;

• Note that users with zero transactions in a month have no row, so join to a calendar (or user × month grid) to catch full drop-offs.

• Goal: incremental transactions or users, not just total redemptions.
• Design: a randomised holdout (or geo test); a primary metric (incremental transacting users, GMV); guardrails (cost per incremental user, fraud/abuse, post-campaign retention).
• Analysis: lift vs holdout with confidence intervals; ROI = incremental margin − cashback cost; check cannibalisation and pull-forward effects; segment by new vs existing users.
• Recommendation: continue for the segments with positive ROI and stop elsewhere.

• Signals:
• Many accounts on one device.
• Shared bank accounts or UPI handles.
• Rapid sign-up → redeem → dormant patterns.
• Circular P2P transfers.
• Emulator or rooted-device flags.
• Methods: rules for known patterns, a graph (connected components on device/bank/phone links), anomaly scores, and a supervised model once labels exist.
• Actions: hold rewards for review, set velocity limits, require KYC for higher tiers. Track the false-positive impact on genuine users.

• WHERE filters rows before aggregation; HAVING filters groups after aggregation (e.g. HAVING COUNT(*) > 5).
• Pitfall: LEFT JOIN b ... WHERE b.col = 'x' turns it into an inner join (rows with NULLs are dropped). Put the condition in the ON clause to keep the unmatched left rows.