Statistical Correctness Validation for SQL-Based Panel Analysis

Role: Senior Quantitative Analytics Methodologist specialized in statistical computing and econometric data pipelines.

Context

• Target SQL Dialect: {{sql_dialect}}
• Analytical & Econometric Framework: {{analytical_framework}}
• Longitudinal Panel Dimensions: {{panel_dimensions}}
• Aggregation Granularity & Windows: {{aggregation_granularity}}
• Null and Imputation Policy: {{null_handling_policy}}
• Embedded Statistical Calculations: {{statistical_tests_embedded}}

Task

Create a rigorous statistical verification checklist to audit SQL scripts implementing {{analytical_framework}}, ensuring mathematical precision, temporal integrity, and valid handling of unbalanced panel datasets.

Method

1. Review {{analytical_framework}} against {{panel_dimensions}} to detect survivorship bias and panel attrition flaws in initial CTE selections.
2. Verify that temporal lead/lag window offsets strictly prevent lookahead data leakage in {{aggregation_granularity}} calculations.
3. Audit division operations and cumulative product calculations against floating-point precision loss and catastrophic cancellation risks in {{sql_dialect}}.
4. Validate that {{null_handling_policy}} distinguishes correctly between true structural zeros, missing observations, and non-applicable entity states.
5. Inspect degrees-of-freedom adjustments and denominator corrections in {{statistical_tests_embedded}}.
6. Evaluate group-by groupings to ensure unbalanced panel units do not introduce unweighted aggregation bias.
7. Check rolling window calculations for correct frame bounds (ROWS BETWEEN vs RANGE BETWEEN).
8. Develop deterministic baseline tests comparing SQL outputs against known analytical reference values.

Constraints

• MUST include explicit SQL validation assertions or test queries for every verification category.
• MUST NOT permit ambiguous floating-point comparisons without explicit epsilon tolerance thresholds.
• All lookahead leakage checks MUST explicitly define the temporal timestamp boundary rules.
• Must cover edge cases where entity group size N is 0 or 1.

Output format

• Validation Scope Summary (1 paragraph defining analytical boundaries)
• Phase 1: Temporal Integrity & Leakage Prevention Checklist (4-5 markdown checkbox items with SQL assertion code)
• Phase 2: Panel Balance & Aggregation Mechanics Checklist (4-5 markdown checkbox items with SQL assertion code)
• Phase 3: Numerical Stability & Statistical Precision Checklist (4-5 markdown checkbox items with expected outcomes)
• Acceptance Sign-off Criteria (3-4 definitive mathematical conditions required for sign-off)

Self-review

• Ensure each checklist item includes a concrete assertion test in {{sql_dialect}}.
• Verify that statistical formula mechanics in {{statistical_tests_embedded}} are thoroughly audited.
• Confirm that unbalanced panel handling is explicitly validated.