How do you optimize a slow-running SQL query that joins multi-terabyte R&D data repositories with complex aggregations?
Write a PostgreSQL query that returns study-level metrics for completed studies that started in 2025. The query should exclude invalid assay results and assay measurements outside calendar year 2025 while preserving eligible studies with no matching experiments or results.
Output
- One row per eligible study, with
study_code, experiment_count, compound_count, valid_assay_count, avg_measured_value, and max_toxicity_score
- Include eligible studies with zero matching results, using zero counts and NULL averages or maximums where appropriate
- Order by
study_code ascending
