QUALIFY: First Purchase — Solution & ExplanationProblems / QUALIFY: First Purchase / EditorialApproach

Rank within each customer. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_time) marks the earliest purchase with 1.

Filter with QUALIFY. Because SQL's logical order evaluates window functions after WHERE and HAVING, filtering on a window result classically requires wrapping in a subquery. QUALIFY is the purpose-built clause that runs after window evaluation — the query collapses to a single SELECT.

PySpark equivalence. No QUALIFY exists there; withColumn + filter is the desugared form — worth seeing side by side, because it makes clear QUALIFY is syntax, not new capability.

Key InsightSQL's filtering clauses map one-to-one onto its evaluation phases: WHERE → rows, HAVING → groups, QUALIFY → window results. Deduplication-by-recency ("latest row per key", "first event per user") is such a common operation that this phase-aware clause pays for itself immediately — and knowing *why* it exists (window functions evaluate late) is the same knowledge that explains why WHERE rn = 1 fails without it.Hints

WHERE can't see window functions (it runs before them) — that's why the classic version needs a subquery. QUALIFY runs AFTER window evaluation, so it can filter on ROW_NUMBER directly.

The clause order mnemonic: WHERE filters rows, HAVING filters groups, QUALIFY filters window results.

PySpark has no QUALIFY — the withColumn(rn) → filter(rn == 1) → drop pattern is the equivalent, and it's exactly what QUALIFY desugars to.

Solve QUALIFY: First Purchase yourself →