Cohort Revenue (30-Day LTV) — Solution & ExplanationProblems / Cohort Revenue (30-Day LTV) / EditorialApproach

Cohort label. DATE_TRUNC('month', signup_time) puts every customer in a first-of-month bucket.

Windowed join. LEFT JOIN orders with the *entire* qualification in the ON clause: same customer, order_time >= signup_time, order_time < signup_time + INTERVAL 30 DAY. Each customer's window is different — that's what makes this an ON-clause predicate rather than a WHERE filter.

Aggregate. COUNT(DISTINCT customer_id) for cohort size (the join fans out customers with several orders), SUM(amount) for revenue, COALESCE for all-quiet cohorts, ROUND(..., 2).

The PySpark version splits the same logic into two aggregates (cohort sizes; windowed revenue) joined at the end — often clearer than a fanned-out single pipeline, and it avoids the DISTINCT-under-fan-out subtlety entirely.Key Insight"Revenue in the first 30 days" is a per-entity relative window. The classic mistake is filtering with a calendar boundary (orders in the signup month, or before some fixed date), which gives January signups up to 30 more earning days than late-January ones. The second classic mistake is WHERE-ing the window predicate after a LEFT JOIN — the NULL order rows fail the predicate and the join quietly becomes inner, so zero-spend customers vanish from customers. Window in the ON clause; population intact.Hints

The 30-day window is anchored to each customer's own signup_time, so the cutoff must reference the joined customer row — it cannot be a constant date filter.

Put the window predicate in the LEFT JOIN's ON clause. Moving it to WHERE turns the join inner and drops customers with no qualifying orders — shrinking cohort sizes.

After a LEFT JOIN a customer with several orders appears several times, so cohort size needs COUNT(DISTINCT customer_id); SUM(amount) is safe because each order row appears once.

The 'strictly before +30 days' boundary matters: an order exactly at signup + 30 days is planted in the data and must be excluded.

Solve Cohort Revenue (30-Day LTV) yourself →