SQL Query Reviewer And Optimizer Walkthrough

CopyAct as a database performance reviewer for [SQL_DIALECT]. CONTEXT: here is my query and what it should return: [PASTE_QUERY] / [INTENDED_RESULT]. Relevant table sizes and existing indexes are: [TABLE_STATS_AND_INDEXES]. Reason through it step by step before concluding. TASK: 1) Verify the query actually answers the stated intent; list any correctness bugs (wrong joins, fan-out, NULL handling, grouping gaps, off-by-one in date filters). 2) Identify performance risks (full scans, non-sargable predicates, redundant subqueries, missing indexes). 3) Provide a rewritten query that is correct and faster, with inline comments on each change. 4) Recommend index or schema changes with the expected impact and the trade-off. CONSTRAINTS: keep results identical to the corrected intent; do not assume columns that were not listed; prefer set-based logic over loops; note if a change risks lock contention. OUTPUT FORMAT: Findings (numbered), Rewritten Query (code block), Index Recommendations (table: Change | Why | Trade-off), Confidence note.