Multi-CTE Pipeline — Solution & ExplanationProblems / Multi-CTE Pipeline / EditorialApproach

CTE 1 — completed_orders: WHERE status = 'completed'. Pending and cancelled orders exit the pipeline here.

CTE 2 — customer_revenue: reads FROM completed_orders, groups by customer_id, and computes SUM(amount) AS total_revenue. Later CTEs can reference earlier ones, which is what makes them a pipeline.

Final query: join customer_revenue back to customers to attach customer_name, keep total_revenue > 500 (strictly greater), and order by total_revenue DESC, customer_id.

The PySpark translation is one intermediate DataFrame per CTE: completed_orders = orders.filter(...), then customer_revenue = completed_orders.groupBy(...).agg(...), then filter + join + select.Key InsightStage order is the whole game. Filtering by status must happen before the SUM — a customer with $450 completed plus $200 cancelled has $650 total but only $450 of real revenue, and must not appear. And the revenue threshold must be applied after the SUM (on the aggregate, not on individual order amounts): a customer can cross $500 through many small orders none of which exceeds $500 alone. CTEs make that ordering explicit and readable; an inner join to customers suffices since customers with no completed revenue can't clear the threshold anyway.Hints

Chain CTEs with commas: WITH completed_orders AS (...), customer_revenue AS (...) SELECT ... — the second CTE reads FROM the first.

Stage 1 is just a WHERE status = 'completed'; stage 2 is GROUP BY customer_id with SUM(amount) AS total_revenue over the first CTE.

Filter the aggregated revenue in the final query with WHERE total_revenue > 500 — strictly greater, so a customer at exactly $500.00 is excluded.

Filter orders by status *before* summing. If you sum first, pending and cancelled amounts sneak into the totals.

Solve Multi-CTE Pipeline yourself →