Practice Problems — SQL & PM Cases

WITH cohort AS (
  SELECT applicant_id, DATE(created_at) AS started_on, acquisition_source
  FROM onboarding.applicants
  WHERE created_at >= '2026-01-01'
    AND created_at <  CURRENT_DATE - INTERVAL '7 days' -- exclude young
),
first_act AS (
  SELECT applicant_id, MIN(occurred_at) AS first_deposit_at
  FROM onboarding.events
  WHERE event_type = 'first_deposit'
  GROUP BY applicant_id
)
SELECT
  c.acquisition_source,
  COUNT(*)                                                        AS cohort_n,
  COUNT(CASE WHEN f.first_deposit_at <= c.started_on + INTERVAL '7 days'
             THEN 1 END)                                          AS activated_7d,
  ROUND(100.0 * COUNT(CASE WHEN f.first_deposit_at <= c.started_on + INTERVAL '7 days'
                           THEN 1 END) / NULLIF(COUNT(*), 0), 2)  AS d7_pct
FROM cohort c
LEFT JOIN first_act f USING (applicant_id)
GROUP BY 1
ORDER BY cohort_n DESC;

Talking points: exclude the last 7 days of cohorts because they haven't had a chance to convert; LEFT JOIN so non-converters stay in the denominator; NULLIF guards against divide-by-zero in tiny segments.

Proposal: "Median time-to-launch a new onboarding configuration (new market, new tier, or new vendor) measured from kickoff to first-verified-customer."

Defense: Directly measures the team-as-customer outcome the platform exists to enable; trends are interpretable; product-launch dependency makes it visible to leadership.

Critique: Not directly tied to revenue; can be gamed by trivial launches; doesn't capture quality of the resulting flow.

Paired: (a) overall verified-customer activation rate by segment/region; (b) cost per successful verification.

Guardrails: (a) sanctions hit rate must not decline; (b) customer complaint volume must not rise; (c) audit-log completeness stays 100%.

• Interface: create_session, get_decision, fetch_evidence, cancel.
• Normalized: outcome (approve/refer/reject), confidence (0-1 calibrated), decision_id, evidence URLs. Vendor field stays in payload but downstream code reads only normalized.
• Vendor-specific: raw signal payloads preserved in signals dict for audit; capability flags (e.g. supports_age_estimation) so downstream can opt into vendor-unique features additively.
• Audit: every call writes an event with vendor, version, decision_id; raw vendor response stored encrypted with retention aligned to AML window.
• Rollout: shadow-mode first (call both vendors, compare outcomes, don't change customer experience) for 2 weeks; then 1% canary; then ramp.

• Stage 1 — Shadow mode (2 weeks). Route 100% to A. Also call B for every applicant. Compare outcomes; calibrate scores; verify B's evidence shape integrates. Customer experience unchanged.
• Stage 2 — Holdout A/B (4 weeks). 50/50 split on new applicants in DE. Primary metric: approval rate. Guardrails: false-accept (proxy: fraud rate 90d post), customer-complaint volume, decision latency p95.
• Stage 3 — Ramp. If B wins approval rate at non-inferior latency and guardrails hold, ramp to 100%. A stays as failover.
• Cohort/segment: stratify by document type — vendors differ wildly on passport vs national-ID.
• Power: calculate sample size for the smallest effect you'd care about (e.g. 1pp approval lift). For the company volumes, usually weeks not months.
• Stop conditions: guardrails breach (false-accept up by > 0.5pp, complaints up > 20%) → pause.

Top-down: Brazilian crypto-curious market ~ X million (publicly cited estimates from Chainalysis, Statista — flag the source). Realistic year-1 share for a foreign exchange entering: 0.5-2%. Net new sign-ups: range.

Bottom-up: From comparable market launches (a similar-sized LATAM country): N sign-ups in month-1, decaying to steady-state N/month. Annualize.

Funnel application: apply our consumer funnel rates (start → verified → activated) — usually new markets convert below baseline initially. Discount by 20-30% the first quarter.

Revenue: activated customers × ARPU for that segment × retention curve.

Costs: per-verification cost × verifications; local-language support; compliance setup amortized.

What I'd flag: ranges, not point estimates. The big risk isn't the model — it's the regulatory timeline (license to operate).

• Default deploy window: Tuesday-Thursday 10am-3pm local. Anything outside requires explicit approval.
• Classification matters: bug fixes and config changes are always allowed; new flows / policy changes go through the window.
• Pre-launch checklist: for material changes, requires a named on-call for the next 24h, a rollback plan in writing, and a canary stage.
• Emergency override: a written justification, logged, reviewed weekly.
• Quarterly review: are we shipping less? Are regressions down? Adjust the window if too restrictive.
• Communication: publish the policy to all platform consumers; surprise restrictions are the actual problem.

1. First — confirm the drop is real, not a measurement artifact. Compare cohorts of equal age.
2. Decompose by funnel step — which step dropped?

SELECT
  DATE_TRUNC('week', a.created_at) AS cohort_week,
  COUNT(*)                                                        AS started,
  COUNT(CASE WHEN e1.event_type='id_submitted' THEN 1 END)        AS submitted,
  COUNT(CASE WHEN e2.event_type='verified'      THEN 1 END)        AS verified,
  COUNT(CASE WHEN e3.event_type='approved'      THEN 1 END)        AS approved,
  COUNT(CASE WHEN e4.event_type='first_deposit' THEN 1 END)        AS activated
FROM onboarding.applicants a
LEFT JOIN onboarding.events e1 ON e1.applicant_id=a.applicant_id AND e1.event_type='id_submitted'
LEFT JOIN onboarding.events e2 ON e2.applicant_id=a.applicant_id AND e2.event_type='verified'
LEFT JOIN onboarding.events e3 ON e3.applicant_id=a.applicant_id AND e3.event_type='approved'
LEFT JOIN onboarding.events e4 ON e4.applicant_id=a.applicant_id AND e4.event_type='first_deposit'
WHERE a.region = 'DE'
  AND a.created_at >= CURRENT_DATE - INTERVAL '60 days'
GROUP BY 1
ORDER BY 1;

Then slice by: vendor (did we route to a different one?), document type, app version, time-of-day (is it traffic mix?), referral source. Most "activation drops" trace to a single explanatory dimension.

• Cohort: "tier=2 AND policy_version < new". 1.2M.
• Risk-score and prioritize: high-volume / high-balance customers first; long-dormant customers can be batched later.
• Step-up flow: the customer doesn't redo full KYC — only the delta (collect source-of-funds, light verification).
• Trigger: on next login, on next material action (deposit / withdrawal above threshold), or scheduled comms.
• Soft-block ladder: day 0 — notify in-app; day 30 — banner can't dismiss; day 60 — disable new deposits; day 90 — disable trading. Each gate Compliance-approved.
• Comms: joint with Compliance and Growth; multi-channel (email, in-app, push).
• Ops queue: for customers whose step-up needs manual review.
• Measurement: completion rate by cohort week, complaint volume, churn delta.
• Audit: every customer's record cites the campaign and the date of step-up.

Data model: screening_hit(hit_id, applicant_id, list, list_entity, match_score, fields_matched, detected_at, disposition, dispositioned_by, dispositioned_at, rationale, attachments).

UX must surface: side-by-side of applicant vs list entry (name, DOB, nationality); match-score and which fields matched; prior dispositions for same entity; one-click outcomes (true-match → escalate, no-match → close with rationale).

SLA: high-confidence hits (score > 0.9) reviewed within 4 hours; lower within 24. Customer account state during pending: soft-blocked from new transactions, communicated via banner.

Audit: every disposition logged with reviewer identity and rationale. Re-screening hits on previously-cleared customers surface the prior disposition.

Quality controls: sample audit of cleared hits (10% review by a second reviewer), feedback loop into the match-score calibration.

Days 1-30 — Learn. 1:1s with: VP-Eng, head of Compliance, head of Ops, three consumer-team PMs (Pro, Futures, Institutional), one operations lead, one customer-support lead. Shadow two manual reviews. Read the AML risk assessment. Read recent postmortems. Map the platform's current capabilities and limits. Result: a written current-state document, shared.

Days 30-60 — Diagnose. Synthesize the listening into 3-5 named gaps. Get Compliance and Eng to agree on which two are highest-leverage. Draft the platform thesis. Pre-wire with VP-Product and VP-Eng. Result: a strategy doc circulated for review.

Days 60-90 — Commit. Strategy doc approved. First 90-day wave scoped: one named primitive, one named consumer-team partner, one named launch unlock. Eng plan in place. Compliance counterpart named. Result: the team is shipping, with a roadmap people can reference.

What I'd avoid: announcing a vision in week 2; promising specific dates before knowing the team's capacity; making enemies with Compliance by skipping their review.