Time to Convert — Solution & ExplanationProblems / Time to Convert / EditorialApproach

First purchase per user. GROUP BY user_id with MIN(purchase_time). This is the step that makes "multiple purchases" harmless — every user is reduced to a single conversion moment.

Inner join to signups. Converters keep their row; never-purchasers drop out — the join *is* the filter. The channel column rides along from the signup side.

Gap and average. DATE_DIFF('hour', signup_time, first_purchase_time) per user, then AVG per channel, ROUND(..., 2). In PySpark under SQLFrame use F.expr("TIMESTAMPDIFF(HOUR, ...)") — writing DATE_DIFF inside F.expr gets Spark's datediff semantics (both sides cast to DATE), which floors a 6-hour gap to 0.

Key InsightTime-to-convert is a per-entity duration averaged at a coarser grain — and the wrong-grain version is seductively easy to write. AVG over a signups×purchases join weights users by how *many* purchases they made: a shopaholic with 10 orders pulls the channel average toward their later purchases. Collapsing to first-purchase-per-user *before* joining fixes the weighting so every converter counts exactly once.Hints

Reduce purchases to one row per user first: MIN(purchase_time). Joining signups straight to raw purchases averages over every purchase, not every user.

An INNER JOIN between signups and the first-purchase CTE silently implements 'exclude non-converters' — no WHERE clause needed.

DuckDB's DATE_DIFF('hour', start, end) returns the whole-hour gap; averaging those per channel and rounding to 2dp finishes the job.

Sanity-check the join direction: channel lives on the signup row, so signups must be the driving table that purchases attach to.

Solve Time to Convert yourself →