Practice — SQL Mental Model & Query Execution Order (4 questions)

A junior analyst on your team writes the following query and asks you why it throws column "recent_flag" does not exist:

SELECT
  customer_id,
  order_date,
  CASE WHEN order_date >= CURRENT_DATE - INTERVAL '30 days'
       THEN 1 ELSE 0 END AS recent_flag
FROM orders
WHERE recent_flag = 1
ORDER BY recent_flag DESC;

They point out that recent_flag is right there in the SELECT list, so they don't understand why the database can't see it in the WHERE clause but apparently can in ORDER BY (which works fine once WHERE is removed).

1. Explain, using the logical query processing order, exactly why the WHERE clause fails but ORDER BY would succeed referencing the same alias.
2. Rewrite the query so it filters correctly for "orders in the last 30 days," without changing what the query is trying to accomplish.
3. The analyst's next question: "so is GROUP BY able to use an alias from SELECT too, or is it like WHERE?" Answer this precisely, including why the answer isn't as clean-cut as WHERE's case.

A finance analyst reports that a daily revenue-by-region report is showing numbers roughly 2-3x too high compared to the source system. The query looks like this:

SELECT
  c.region,
  SUM(o.amount) AS total_revenue
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_line_items li ON o.order_id = li.order_id
GROUP BY c.region;

Each orders row has a total amount, and each order can have multiple rows in order_line_items (one per product in the order). The analyst insists the query "looks right" because every JOIN correctly matches on a real foreign key with no typos.

1. Diagnose exactly why SUM(o.amount) is inflated, reasoning about row counts the way this subject teaches (not just "joins are tricky").
2. Propose two different fixes, and explain the trade-off between them.
3. Suppose you didn't have this line-item detail and just needed to sanity check whether a JOIN is inflating row counts before trusting any aggregate built on top of it — describe a concrete, generic technique for catching this class of bug before it reaches a report.

A marketing team runs this query nightly to find customers eligible for a promotional email — everyone who is not on the suppression list:

SELECT customer_id, email
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM suppression_list
);

For months, this returned a reasonable list of a few thousand eligible customers each night. Last night it returned exactly zero rows, and the team is convinced "the database is broken" because no code changed. You're asked to investigate.

1. What is the most likely root cause, and what single fact about the suppression_list.customer_id column would you check first to confirm it?
2. Explain, mechanically, why this produces exactly zero rows rather than, say, a smaller-than-usual but nonzero list.
3. Propose a fix, and explain why it's more robust than just "clean the NULL out of suppression_list today," which only fixes today's symptom.

A teammate writes this query to find departments that hired more than 5 people in 2020 or later, and it fails with a database error:

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE hire_date >= '2020-01-01' AND COUNT(*) > 5
GROUP BY department;

1. Identify precisely which part of this query is invalid, and explain why using the logical query processing order — don't just say "aggregates can't go in WHERE," explain why they can't.
2. Fix the query so it correctly answers the original question: departments with more than 5 hires since 2020-01-01.
3. Now extend the question: the team also wants to see the average salary of recent hires in those departments, but only counting employees hired in 2020 or later toward both the headcount and the average — employees hired earlier should not affect the average either. Write the full query and explain which clause is responsible for excluding the pre-2020 employees from the average salary calculation, and why that's the right clause for the job.