A data analyst writes the following query to find customers who have made at least one purchase in 2025:
SELECT customer_id FROM orders WHERE year = 2025 GROUP BY customer_id HAVING COUNT(*) >= 1;
The query runs but returns far fewer customers than expected. After investigation, the analyst finds the orders table has a column named 'purchase_year' (not 'year'). The WHERE clause references a non-existent column. How should the query be fixed?
Answer & explanation
Correct answer: E
WHY E: The root cause is a misspelled column name in the WHERE clause (year instead of purchase_year). The correct fix is straightforward — replace 'year' with 'purchase_year' to reference the actual column. The rest of the query logic (GROUP BY + HAVING COUNT(*) >= 1) is correct. WHY NOT A: SQL does not support AS aliases inside WHERE clauses to rename column references; this syntax is invalid. WHY NOT C: Moving the column filter to HAVING is technically possible but non-standard, less efficient (filters all rows through GROUP BY before applying the year condition), and doesn't fix the root cause of referencing the wrong column name. WHY NOT D: The table has one fixed schema; CASE WHEN to handle different column names in different partitions is not valid for this scenario and would not resolve the misspelling. WHY NOT B: A table alias qualifies the column but does not rename it; o.year still references a non-existent column and would still fail.