Above Category Average Spend — Solution & ExplanationProblems / Above Category Average Spend / EditorialApproach

Compute each category's average price. In SQL this can be a correlated subquery — for every product row, (SELECT AVG(price) FROM products p2 WHERE p2.category = p.category) recomputes the average of just that product's category.

Keep rows where price > that average. Strictly greater: the Chef Knife priced exactly at its category average (30.00) is excluded, and the Toys category with a single product can never beat its own average.

Order by category, then product_id.

Key InsightA correlated subquery re-evaluates once per outer row, which reads naturally but hides a join. The equivalent set-based form — a CTE of per-category averages joined back to products — is exactly what PySpark forces you to write (groupBy("category").agg(F.avg(...)) plus a join), and is usually how the optimizer executes the correlated version anyway.Hints

You need a different average for every row — the subquery must reference the outer query's category, so it is re-evaluated per product.

WHERE p.price > (SELECT AVG(price) FROM products p2 WHERE p2.category = p.category) is the correlated form. A CTE of per-category averages joined back works just as well.

Watch the strictness: one product sits exactly at its category average and must be excluded, and a single-product category can never beat its own average. In PySpark, compute averages with groupBy + avg, join back, and filter.

Solve Above Category Average Spend yourself →