IQR Outliers Within Each Category — Solution & ExplanationProblems / IQR Outliers Within Each Category / EditorialApproach

Aggregate per category. A CTE computes PERCENTILE_CONT(0.25) and PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) for each category. PERCENTILE_CONT interpolates linearly between adjacent values — with 8 rows, Q1 sits at position 1.75 and is a weighted blend of the 2nd and 3rd smallest amounts. This matches pandas' default quantile(0.25) exactly.

Join back. Rejoin the stats to transactions on category, so each row sees its own category's q1/q3.

Flag strictly outside the fences. is_outlier = amount < q1 - 1.5*IQR OR amount > q3 + 1.5*IQR. The comparisons are strict: fences are inclusive bounds, and the dataset plants one transaction whose amount lands exactly on its category's upper fence — <=/>= would misflag it.

Order by txn_id.

In PySpark/SQLFrame there is no native percentile_cont function object, but F.expr("percentile_cont(0.25) within group (order by amount)") inside .agg() passes the ordered-set aggregate straight through to DuckDB.Key InsightOutlier detection is only meaningful relative to a peer group: 100 is a wild outlier among dining bills of 5–11 but would be the cheapest row in travel, and travel's genuine outlier (2) sits comfortably inside every other category's fences. The aggregate-then-rejoin pattern is the standard way to compare each row against group-level statistics. The second discipline this problem enforces is boundary semantics: "1.5 × IQR" fences are conventionally inclusive, so outliers are *strictly* outside — an off-by-one in the comparison operator flips exactly one row in this dataset.Hints

Two-step shape: a CTE that aggregates each category down to (q1, q3), then a join back to transactions so every row can be compared against its own category's fences.

PERCENTILE_CONT is an ordered-set aggregate — the syntax is PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount). It linearly interpolates between values, matching pandas' default quantile(); PERCENTILE_DISC would pick an actual row value and give different fences.

The flag is a boolean expression, not a CASE ladder: amount < q1 - 1.5*iqr OR amount > q3 + 1.5*iqr. Strictly outside — one transaction sits exactly ON its category's upper fence and must be flagged FALSE (< and >, not <= and >=).

In PySpark/SQLFrame there is no F.percentile_cont; put the ordered-set aggregate inside F.expr(...) within .agg(), rename the grouped category column, and join back.

Solve IQR Outliers Within Each Category yourself →