Customer Classification Engine — Solution & ExplanationProblems / Customer Classification Engine / EditorialApproachThis problem tests the ability to translate multi-condition business rules into SQL CASE expressions or PySpark when/otherwise chains.Key Concepts

Ordered CASE evaluation: CASE WHEN returns the first matching branch. Put more specific conditions (Platinum) before less specific ones (Silver).

Compound conditions: The risk flag uses OR to combine two independent risk criteria, one of which is itself an AND of two conditions. Parentheses are critical.

Derived columns: In PySpark, withColumn("discount_pct", F.when(F.col("tier") == "Platinum", 20)...) references the previously computed tier column. In SQL, you'd need to repeat the CASE logic or use a CTE.

Common PitfallForgetting that return_rate > 0.2 AND avg_order_value < 50 must be grouped with parentheses when combined with OR, or the precedence will produce wrong results.Hints

CASE WHEN evaluates conditions top-to-bottom and returns the first match — order matters for tier assignment.

The risk flag combines OR and AND with parentheses. Make sure the AND group is parenthesized correctly.

In PySpark, use & for AND and | for OR inside F.when(). Each condition must be wrapped in parentheses.

Solve Customer Classification Engine yourself →