Orders Exceeding Customer Average — Solution & ExplanationProblems / Orders Exceeding Customer Average / EditorialApproach

Compute each customer's average order amount. Correlated form: (SELECT AVG(amount) FROM orders o2 WHERE o2.customer_id = o.customer_id), re-evaluated for every outer row. Set-based form: a per-customer aggregate (CTE or groupBy("customer_id")) joined back on customer_id — this is the only form available in PySpark.

Filter amount > 1.5 * avg. Note the average includes the order being tested, which dampens outliers: a huge order raises its own threshold.

Return the average as customer_avg rounded to 2 decimal places, and order by order_id.

Key InsightTwo customers in the data show why the boundaries matter. Orders of 50 and 150 average to 100 — the 150 order equals exactly 1.5× and the strict inequality excludes it. Three identical 80 orders can never be flagged: when every amount equals the average, nothing exceeds 1.5× of it. Also note the rounding split: compare against the raw average and round only the displayed column — rounding before comparing can flip boundary cases.Hints

Each row needs the average of its own customer's orders — a correlated subquery on customer_id, or a per-customer aggregate joined back on customer_id.

Compare against the raw (unrounded) average; only the returned customer_avg column is rounded to 2 decimals.

Strictly greater than 1.5×: a customer with orders of 50 and 150 has average 100, so the 150 order sits exactly at the threshold and is NOT flagged. A customer whose orders are all identical is never flagged.

Solve Orders Exceeding Customer Average yourself →