Power Users by Percentile Cutoff — Solution & ExplanationProblems / Power Users by Percentile Cutoff / EditorialApproach

Per-user counts. GROUP BY user_id with COUNT(*) collapses the raw event log to one row per user.

Single cutoff, not per-row ranks. PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY event_count) computes one interpolated value over the 11 per-user counts. With n = 11, the 0.9 quantile sits at position (11-1) x 0.9 = 9 exactly, so the cutoff equals the second-highest count — no interpolation blending needed, and it matches pandas quantile(0.9) (linear interpolation) exactly.

Classify against the cutoff. CROSS JOIN the 1-row cutoff onto the counts and label event_count >= p90 as 'power'. The comparison is inclusive: one user's count lands exactly on the percentile value and belongs in 'power'.

Order by user_id.

In PySpark/SQLFrame there is no F.percentile_cont function object; F.expr("percentile_cont(0.9) within group (order by event_count)") inside .agg() passes the ordered-set aggregate through to DuckDB, and .crossJoin attaches the scalar to every row.Key InsightThis is the *cutoff* flavor of percentile logic, distinct from percent_rank(): instead of scoring every row relative to the others, you reduce the distribution to one threshold value and classify against it. That two-grain shape (aggregate → scalar → rejoin) is the standard way to compare rows against a global statistic, and it generalizes to any "top X% by volume" segmentation. The boundary detail matters: percentile cutoffs are conventionally inclusive, and ties around the threshold (this data has duplicate counts throughout) mean >= vs > changes who gets the power-user badge.Hints

Two aggregations at different grains: first collapse events to one count per user, then collapse those counts to a single percentile value. A CTE for each step keeps the grains separate.

PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY event_count) is an ordered-set aggregate producing exactly one row — attach it to every user with a CROSS JOIN (a 1-row join is cheap).

The cutoff is inclusive: event_count >= p90 is 'power'. The data plants a user whose count lands exactly ON the interpolated percentile — a strict > would wrongly demote them to 'regular'.

In PySpark/SQLFrame there is no F.percentile_cont; wrap the ordered-set aggregate in F.expr(...) inside .agg(), then .crossJoin(cutoff) back onto the per-user counts.

Solve Power Users by Percentile Cutoff yourself →