O1Easy★ BA/DA to DS/MLE BridgePart O · BA-to-Python Data Science & Stats Kernels
Vectorized Cumulative Cohort ARPU Matrix (SQL to NumPy)
Replace slow Python loops and SQL self-joins with np.bincount, np.add.at scatter-add, and np.cumsum for exact LTV triangle matrices.
⏱️ Time:
O(U + E + C*W)💾 Space:
O(C*W)🎯 Interactive Self-Practice Loop (Attempt Before Peeking)
Practice in 4 steps: recall mechanism, complete core lines, check edge cases, and log mastery locally.
Step 1 · Mechanism Recall & Stability Self-Check
Step 2 · Complete Key Implementation Lines
import numpy as np
def compute_cohort_arpu_matrix(signup_cohorts: np.ndarray, event_offsets: np.ndarray, revenues: np.ndarray, num_cohorts: int, max_window: int) -> np.ndarray:
# signup_cohorts: (N_users,), event_offsets & revenues: (N_events,) paired with user_ids via event_user_idx
raise NotImplementedError('Use explicit signature below')
def cohort_arpu_matrix(user_cohort: np.ndarray, event_user_idx: np.ndarray, event_offset: np.ndarray, event_rev: np.ndarray, num_cohorts: int, max_offset: int) -> np.ndarray:
cohort_sizes =
valid = (event_offset >= 0) & (event_offset <= max_offset)
ev_c = user_cohort[event_user_idx[valid]]
ev_t = event_offset[valid]
ev_r = event_rev[valid]
rev_mat = np.zeros((num_cohorts, max_offset + 1), dtype=np.float64)
np.add.at(rev_mat, (ev_c, ev_t), ev_r)
denom = np.where(cohort_sizes > 0, cohort_sizes, 1.0)[:, None]
arpu = np.where(cohort_sizes[:, None] > 0, rev_mat / denom, 0.0)
return
Step 3 · Verify Boundary Cases & Assertions
Step 4 · Follow-Up Drill & Review Outcome
Q1: Why does rev_mat[ev_c, ev_t] += ev_r silently drop updates?
Q2: How do you prevent right-censored cohorts from biasing day-30 ARPU?
💡
Core Mental Anchor / Mnemonic
bincount denominator, add.at scatter, cumsum LTV
📐 Mathematical Derivation & Core Formula
$$\text{ARPU}_{c,t} = \frac{\sum_{u \in c} R_{u,t}}{|\{u \in c : \text{active}_{u,0}\}|},\quad t \in [0, W]$$
Each cohort denominator equals initial signups via
bincount. Valid revenue events scatter-add into discrete (cohort, day_offset) buckets using unbuffered np.add.at, then divide by frozen cohort size and accumulate along axis=1 to preserve monotonicity.🔄 Tensor Dimensions & Shape Flow
user_cohort: (U,) + event_rev: (E,) -> rev_mat: (C, W+1) -> cum_arpu: (C, W+1)
🛡️ Numerical Stability & Pitfalls Checklist
- Always divide by initial cohort size rather than active daily survivors to prevent survivorship bias
- Use np.add.at instead of fancy-indexed += so duplicate (cohort, day) collisions sum accurately
- Filter negative offsets from clock skew and mask unobserved future days with NaN in production dashboards
💻 Reference Code Implementation
import numpy as np
def compute_cohort_arpu_matrix(signup_cohorts: np.ndarray, event_offsets: np.ndarray, revenues: np.ndarray, num_cohorts: int, max_window: int) -> np.ndarray:
# signup_cohorts: (N_users,), event_offsets & revenues: (N_events,) paired with user_ids via event_user_idx
raise NotImplementedError('Use explicit signature below')
def cohort_arpu_matrix(user_cohort: np.ndarray, event_user_idx: np.ndarray, event_offset: np.ndarray, event_rev: np.ndarray, num_cohorts: int, max_offset: int) -> np.ndarray:
cohort_sizes = np.bincount(user_cohort, minlength=num_cohorts).astype(np.float64)
valid = (event_offset >= 0) & (event_offset <= max_offset)
ev_c = user_cohort[event_user_idx[valid]]
ev_t = event_offset[valid]
ev_r = event_rev[valid]
rev_mat = np.zeros((num_cohorts, max_offset + 1), dtype=np.float64)
np.add.at(rev_mat, (ev_c, ev_t), ev_r)
denom = np.where(cohort_sizes > 0, cohort_sizes, 1.0)[:, None]
arpu = np.where(cohort_sizes[:, None] > 0, rev_mat / denom, 0.0)
return np.cumsum(arpu, axis=1)🧪 Runnable Assertions & Validation
Copy and run directly in Python / Jupyter:
uc = np.array([0, 0, 1, 1])
eu = np.array([0, 1, 0, 2, 3])
eo = np.array([0, 1, 2, 0, 1])
er = np.array([10.0, 20.0, 10.0, 4.0, 6.0])
out = cohort_arpu_matrix(uc, eu, eo, er, 2, 2)
assert np.allclose(out[0], [5.0, 15.0, 20.0]), f'Unexpected cohort 0: {out[0]}'
assert np.allclose(out[1], [2.0, 5.0, 5.0]), f'Unexpected cohort 1: {out[1]}'
print('O1 PASS')🎯 Core Follow-up Inquiries & Reference Answers
Q1:Why does rev_mat[ev_c, ev_t] += ev_r silently drop updates?
NumPy buffered fancy-indexing evaluates rhs once and writes indices sequentially without reading updated slots; np.add.at performs unbuffered atomic-like element accumulation.
Q2:How do you prevent right-censored cohorts from biasing day-30 ARPU?
Track each cohort's observation horizon max_age and mask cells where offset > max_age as NaN before averaging across cohorts.
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.