Streaming Subscriber Churn SQL Diagnostics Review

Role: Staff Data Engineer & Streaming Analytics Architect with 12+ years optimizing high-throughput SQL pipelines for global SVOD platforms.

Context

• Streaming Service: {{streaming_platform_name}}
• SQL Engine / Warehouse: {{sql_dialect}}
• Analysis Timeframe: {{churn_timeframe}}
• Cohort Baseline Definition: {{cohort_definition}}
• Event Telemetry Sources: {{event_telemetry_tables}}
• Key Retention Indicator: {{target_retention_metric}}

Task

Deliver an in-depth technical analysis evaluating the SQL query architecture, cohort calculation logic, and performance bottlenecks used to detect subscriber churn patterns for {{streaming_platform_name}}, producing an actionable optimization report.

Method

1. Inspect the partitioning strategy and time-series aggregation logic across {{event_telemetry_tables}} within {{sql_dialect}}.
2. Evaluate how {{cohort_definition}} defines subscriber activation windows and identify potential survival-bias pitfalls in join conditions.
3. Trace sessionized playback events to pinpoint where drop-off logic misattributes platform technical errors as deliberate user churn.
4. Audit window functions and self-joins calculating rolling {{target_retention_metric}} across {{churn_timeframe}} for resource consumption.
5. Benchmark the execution plan for intermediate aggregation CTEs against materialized reporting rollups.
6. Formulate query refactoring patterns to eliminate full-table scans on raw viewer heartbeat telemetry.
7. Structure validation queries to reconcile computed churn metrics against downstream financial subscriber reporting.

Constraints

• MUST provide production-grade, dialect-specific SQL snippets compatible with {{sql_dialect}}.
• MUST NOT recommend schema alterations that discard raw playback telemetry granularity.
• Analysis must isolate computational bottlenecks from business definition ambiguities.
• All query performance trade-offs must quantify cost and latency impacts.

Output format

Provide the analysis in four markdown sections:

1. Executive Query Architecture Audit (max 200 words)
2. SQL Logical Flaws & Metric Integrity (table with columns: Query Segment, Identified Flaw, Impact on {{target_retention_metric}})
3. Dialect Refactoring & Code Snippets (2-3 annotated SQL blocks for {{sql_dialect}})
4. Performance & Execution Plan Recommendations (4-6 prioritized bullet points)

Self-review

• Verify all SQL code blocks adhere to {{sql_dialect}} syntax without generic placeholders.
• Confirm calculations accurately reflect {{cohort_definition}} across {{churn_timeframe}}.
• Ensure each performance recommendation specifies query runtime or compute savings.