N1Easy★ BA/DS Transition Core · Data BridgePart N · Data Pipelines & Production SysOps
Cohort Retention Matrix (SQL-to-Python Bridge)
Transition from nested SQL self-joins to a single-pass O(E) Python cohort retention matrix with exact user deduplication.
⏱️ Time:
O(E)💾 Space:
O(U * K)🎯 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_retention(signup_day: list, event_day: list, user_ids: list, max_offset: int = 3) -> np.ndarray:
"""
Vectorized Day-0..Day-K cohort retention matrix without nested SQL loops.
Returns matrix M of shape (num_cohorts, max_offset + 1) where M[c, k] is fraction retained.
"""
cohorts = sorted(set(signup_day))
c_to_idx = {c: idx for idx, c in enumerate(cohorts)}
cohort_users = [set() for _ in cohorts]
for uid, sd in zip(user_ids, signup_day):
cohort_users[c_to_idx[sd]].add(uid)
uid_signup = dict(zip(user_ids, signup_day))
ret = np.zeros((len(cohorts), max_offset + 1), dtype=np.float64)
seen =
for uid, ed in zip(user_ids, event_day):
off =
if 0 <= off <= max_offset:
seen[c_to_idx[uid_signup[uid]]][off].add(uid)
for ci, users in enumerate(cohort_users):
denom = float(len(users)) if users else 1.0
for k in range(max_offset + 1):
ret[ci, k] = len(seen[ci][k]) / denom
return ret
Step 3 · Verify Boundary Cases & Assertions
Step 4 · Follow-Up Drill & Review Outcome
Q1: How do you scale this exact pattern across 1B rows in Spark/DuckDB?
💡
Core Mental Anchor / Mnemonic
Hash signup dates once, deduplicate per (cohort, day_offset) bucket, and guard empty cohorts.
📐 Mathematical Derivation & Core Formula
### Single-Pass O(E) Cohort Aggregation
SQL self-joins explode shuffle I/O on billions of raw clickstream rows. Indexing
SQL self-joins explode shuffle I/O on billions of raw clickstream rows. Indexing
uid -> signup_day in a hash map lets one pass compute exact deduplicated Day-0..Day-K retention rates without double-counting power users.🔄 Tensor Dimensions & Shape Flow
(user_ids, signup_day, event_day) [E] -> offset diff -> deduplicated sets[C, K+1] -> float64 matrix
🛡️ Numerical Stability & Pitfalls Checklist
- Deduplicate unique user IDs per
(cohort, offset)bucket so multiple events on one day never inflate retention above 100%. - Clamp offset bounds strictly (
0 <= offset <= max_offset) to ignore late/corrupted timestamps prior to signup.
💻 Reference Code Implementation
import numpy as np
def compute_cohort_retention(signup_day: list, event_day: list, user_ids: list, max_offset: int = 3) -> np.ndarray:
"""
Vectorized Day-0..Day-K cohort retention matrix without nested SQL loops.
Returns matrix M of shape (num_cohorts, max_offset + 1) where M[c, k] is fraction retained.
"""
cohorts = sorted(set(signup_day))
c_to_idx = {c: idx for idx, c in enumerate(cohorts)}
cohort_users = [set() for _ in cohorts]
for uid, sd in zip(user_ids, signup_day):
cohort_users[c_to_idx[sd]].add(uid)
uid_signup = dict(zip(user_ids, signup_day))
ret = np.zeros((len(cohorts), max_offset + 1), dtype=np.float64)
seen = [[set() for _ in range(max_offset + 1)] for _ in cohorts]
for uid, ed in zip(user_ids, event_day):
off = ed - uid_signup[uid]
if 0 <= off <= max_offset:
seen[c_to_idx[uid_signup[uid]]][off].add(uid)
for ci, users in enumerate(cohort_users):
denom = float(len(users)) if users else 1.0
for k in range(max_offset + 1):
ret[ci, k] = len(seen[ci][k]) / denom
return ret
🧪 Runnable Assertions & Validation
Copy and run directly in Python / Jupyter:
import numpy as np
# Cohort Day 0: u1, u2 (u1 active Day 0,1; u2 active Day 0 only -> D1 retention = 0.5)
# Cohort Day 1: u3 (active Day 1, 2 -> D1 retention = 1.0)
signups = [0, 0, 0, 1, 1]
events = [0, 1, 0, 1, 2]
uids = ["u1", "u1", "u2", "u3", "u3"]
mat = compute_cohort_retention(signups, events, uids, max_offset=2)
assert np.allclose(mat[0], [1.0, 0.5, 0.0]), f"Unexpected cohort 0: {mat[0]}"
assert np.allclose(mat[1], [1.0, 1.0, 0.0]), f"Unexpected cohort 1: {mat[1]}"
print("✓ Vectorized cohort retention assertion passed")🎯 Core Follow-up Inquiries & Reference Answers
Q1:How do you scale this exact pattern across 1B rows in Spark/DuckDB?
Dedup
(user_id, event_day) first in map-side combine, join broadcast user signup table, group by (signup_day, datediff) with COUNT(DISTINCT) or integer bitmaps.⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.