Basic CTE Filter — Solution & ExplanationProblems / Basic CTE Filter / EditorialApproach

Define the CTE: WITH active_subs AS (SELECT * FROM subscriptions WHERE status = 'active') names the filtered rows so the rest of the query can treat them as a table.

Aggregate the CTE: SELECT plan, COUNT(*) AS active_count FROM active_subs GROUP BY plan.

Order by plan.

Plans whose subscriptions are all cancelled or paused never reach the GROUP BY, so they simply don't appear — exactly what the problem asks for.In PySpark the same two-step shape falls out naturally: bind the filtered DataFrame to a variable (active_subs = subscriptions.filter(...)), then aggregate it.Key InsightA CTE doesn't change what the query computes — SELECT plan, COUNT(*) FROM subscriptions WHERE status = 'active' GROUP BY plan is equivalent. Its value is readability and reuse: each pipeline stage gets a name, and later problems chain several CTEs where inlining everything would be unreadable.Hints

Structure: WITH active_subs AS (SELECT ... WHERE ...) SELECT ... FROM active_subs. The CTE is just a named subquery.

Inside the CTE, filter with WHERE status = 'active'. The outer query then only ever sees active rows.

The outer query is a plain GROUP BY plan with COUNT(*) — because filtering already happened in the CTE, no WHERE is needed outside.

Solve Basic CTE Filter yourself →