SQL Debugging: Fix an AI-generated query and address data quality issues

Prompt You are given a runnable PostgreSQL database in the interview environment (typically 2–3 dimension tables + 1 fact table). The interviewer provides a purported “AI-generated” SQL query intended to produce an analysis result aligned with the business scenario. Complete both parts: Part A (Debug) Read the SQL and identify/explain at least 4 critical issues (not just syntax): Syntax errors Wrong join types (INNER vs LEFT) Redundant/unnecessary joins Many-to-many joins causing fan-out (row duplication, inflated metrics) Incorrect aggregation semantics (GROUP BY grain mismatch, double counting) Fix the SQL so it runs and produces results with the intended definitions. Part B (Data quality) Run the corrected query and inspect the output. Identify data quality issues in the results (e.g., negative/impossible values, anomalies, bias from missing/orphaned records). Update the SQL to handle these issues (filters, dedupe, NULL handling, constraints, corrected joins) and explain why. Note: You can use the schema explorer to inspect tables/columns/types. This problem tests SQL reading/debugging skills, join semantics, and data quality judgment.