=== SQL REPORTING PLAN FOR NON-TECHNICAL MANAGERS === Business question: How many customers churned last month by plan? Plain-English interpretation: The user wants a count of customers whose subscription ended during the previous calendar month, grouped by subscription plan. Decision supported: Understand which plans had the highest customer churn last month. Metric definition: Metric name: Customer churn count by plan Business definition: Number of customers whose subscription ended during the reporting period and should be counted as churned. Numerator: Count of distinct customers with a subscription status of canceled or ended during last month. Denominator: Not required for churn count. If churn rate is needed, denominator should be active customers at the start of the month. Date logic: Use subscription ended/canceled date, not signup date. Inclusions: Paid customer subscriptions that ended during the period. Exclusions: Trial users, test accounts, internal accounts, and customers who reactivated within the defined grace period if the business uses one. Grouping dimensions: Plan name. Expected grain: One row per plan. Required tables and fields: subscriptions.subscription_id — identify subscription subscriptions.customer_id — identify customer subscriptions.plan_id — connect to plan subscriptions.status — identify canceled/ended subscriptions subscriptions.ended_at or canceled_at — churn date plans.plan_id — join to subscriptions plans.plan_name — group by plan customers.customer_id — identify customer customers.is_test — exclude test accounts if available Assumptions: 1. subscriptions.ended_at represents the churn date. 2. subscriptions.status = 'canceled' indicates churn. 3. customers.is_test can identify test accounts. 4. Plans are stored in a plans table. 5. SQL dialect is PostgreSQL. Adjust date functions if using another database. Safety check: - Query type: read-only SELECT - Sensitive fields avoided: Yes - Destructive operations: None - Date range included: Yes - Row limit recommended: Not required because output is aggregated SQL reporting plan: 1. Start from subscriptions. 2. Join plans to get plan names. 3. Join customers to exclude test accounts. 4. Filter subscriptions that ended last month. 5. Count distinct customers. 6. Group by plan name. 7. Sort by churn count descending. Readable SQL draft: [The skill returns a read-only SELECT query using CTEs, date filtering, grouping by plan, and no destructive operations.] Plain-English explanation: This query looks for subscriptions marked as canceled during the previous calendar month. It excludes test customers, connects each subscription to its plan, counts unique customers, and shows the result grouped by plan. Validation queries: 1. Check total churn count before grouping. 2. Check whether ended_at has null values for canceled subscriptions. 3. Check whether test accounts are correctly excluded. 4. Compare the total with the existing churn dashboard if available. Data quality risks: - Some churn may be stored as status = 'ended' instead of 'canceled'. - Reactivated customers may need to be excluded depending on business definition. - Trial cancellations may be mixed with paid churn. - Timezone may affect month boundaries. Privacy/sensitivity notes: The report uses aggregate customer counts and does not expose names, emails, or account details. Manager-friendly report summary: For last month, the report shows how many customers churned in each plan. The highest churn plan should be reviewed for onboarding, pricing, product fit, or support issues. This result depends on the chosen churn definition and whether reactivations or trials are excluded. Analyst handoff notes: Confirm the correct churn date field, churn status values, plan table name, and test-account flag before running in production reporting.