SQL Logical Query Processing Order: FROM, WHERE, GROUP BY, HAVING, SELECT

SQL interviews often feel harder than the syntax on the page. A query can look readable from top to bottom, but the database does not reason about it in that written order. That mismatch is why candidates get surprised by aliases that are unavailable in WHERE, LEFT JOIN queries that accidentally become inner joins, and aggregate filters that belong in HAVING instead of WHERE.

The useful interview skill is not reciting a theoretical list. It is being able to say, out loud, which rows exist at each stage of the query. Once you can do that, many common SQL traps become predictable.

The Logical Order of a SQL Query

A typical SQL query is written like this:

SELECT
  customer_id,
  COUNT(*) AS order_count
FROM orders
WHERE order_status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY order_count DESC
LIMIT 10;

But the logical processing order is closer to this:

1. FROM and JOIN: choose the source rows and combine tables.
2. WHERE: remove individual rows before aggregation.
3. GROUP BY: form groups from the remaining rows.
4. HAVING: remove groups after aggregation.
5. SELECT: compute the output expressions and aliases.
6. DISTINCT: remove duplicate result rows, if requested.
7. ORDER BY: sort the result.
8. LIMIT or OFFSET: return the final slice.

Different databases have optimizer details and dialect features, but this mental model is what interviewers usually expect. It explains why row filters, group filters, aliases, and final presentation all behave differently.

Why SELECT Aliases Usually Do Not Work in WHERE

A common mistake is trying to use an alias before it logically exists:

SELECT
  price * quantity AS revenue
FROM order_items
WHERE revenue > 100;

The intention is clear, but WHERE is evaluated before SELECT. At the moment the database is deciding which rows to keep, the alias revenue has not been created yet.

Use the expression directly, or wrap the computation in a subquery or CTE:

WITH line_items AS (
  SELECT
    order_id,
    price * quantity AS revenue
  FROM order_items
)
SELECT *
FROM line_items
WHERE revenue > 100;

In an interview, this is a good moment to narrate the tradeoff: repeating the expression can be fine for a small query, but a CTE makes the logic easier to name, test, and reuse.

Where LEFT JOIN Filters Belong

LEFT JOIN keeps every row from the left table, even when the right table has no match. That promise is easy to break by putting a right-table filter in WHERE:

SELECT
  c.customer_id,
  p.payment_id
FROM customers c
LEFT JOIN payments p
  ON c.customer_id = p.customer_id
WHERE p.status = 'success';

Rows with no payment have NULL in p.status. The WHERE clause removes those rows, so the query behaves like an inner join for this condition.

If the goal is to keep all customers and only attach successful payments, put the condition in the join:

SELECT
  c.customer_id,
  p.payment_id
FROM customers c
LEFT JOIN payments p
  ON c.customer_id = p.customer_id
 AND p.status = 'success';

If the goal is to find customers without a successful payment, then filter for the missing match after the join:

SELECT c.customer_id
FROM customers c
LEFT JOIN payments p
  ON c.customer_id = p.customer_id
 AND p.status = 'success'
WHERE p.payment_id IS NULL;

The interview answer should name the business goal first. Are you keeping all left-side rows, finding missing right-side rows, or intentionally requiring a match?

WHERE vs HAVING

WHERE filters rows before aggregation. HAVING filters groups after aggregation.

Use WHERE when the condition applies to individual rows:

SELECT
  customer_id,
  COUNT(*) AS paid_orders
FROM orders
WHERE order_status = 'paid'
GROUP BY customer_id;

Use HAVING when the condition depends on an aggregate:

SELECT
  customer_id,
  COUNT(*) AS paid_orders
FROM orders
WHERE order_status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 3;

A practical rule: if the filter can reduce raw rows before grouping, put it in WHERE. If the filter needs COUNT, SUM, AVG, MIN, or MAX across a group, put it in HAVING.

Window Functions and the QUALIFY Pattern

Window functions create another version of the alias problem. This query is not portable SQL because rn is created in SELECT, after WHERE has already run:

SELECT
  user_id,
  event_time,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_time DESC
  ) AS rn
FROM events
WHERE rn = 1;

The safe pattern is a CTE or subquery:

WITH ranked_events AS (
  SELECT
    user_id,
    event_time,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY event_time DESC
    ) AS rn
  FROM events
)
SELECT user_id, event_time
FROM ranked_events
WHERE rn = 1;

Some warehouses support QUALIFY, which filters after window functions:

SELECT
  user_id,
  event_time
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY event_time DESC
) = 1;

If you use QUALIFY in an interview, say that it is dialect-specific. If you are unsure of the interview database, use the CTE pattern.

A Full Interview Walkthrough

Suppose the prompt is: “Find the top five customers by paid revenue in the last 30 days, excluding refunded orders.”

A strong answer starts with grain and filters:

• The row grain is one order.
• Filter to the last 30 days and paid orders before grouping.
• Exclude refunded orders before calculating revenue.
• Group by customer.
• Sort by total revenue and return five rows.

SELECT
  customer_id,
  SUM(order_total) AS paid_revenue
FROM orders
WHERE order_status = 'paid'
  AND refunded_at IS NULL
  AND created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
ORDER BY paid_revenue DESC
LIMIT 5;

Then mention the edge cases: time zone of created_at, partial refunds, duplicate orders, test accounts, and ties for fifth place. That turns a basic query into an analyst-quality answer.

How to Explain Processing Order in an Interview

Use this short script when you feel pressure:

“I’m going to decide the row set first, then aggregate, then filter groups, then sort the final output. So the date and status filters belong in WHERE. The customer-level threshold belongs in HAVING. If I need to filter on a window function or a derived alias, I’ll put that logic in a CTE and filter in the outer query.”

That explanation does three things at once. It shows you know SQL, it shows you can debug your own query, and it shows that you understand the difference between raw rows and grouped results.

Practice Prompts

• Write a query that returns users with at least three purchases in the last 90 days.
• Find customers who signed up but never made a successful payment.
• Return the latest event per user without relying on a dialect-specific feature.
• Calculate monthly revenue and filter to months above $100,000.
• Find products whose average rating is above 4.5, but only count verified reviews.

For each prompt, say which filters happen before grouping, which filters happen after grouping, and whether any aliases need an outer query.

FAQ

Does SQL always execute in this exact order?

No. The optimizer can rewrite a query internally as long as it preserves the same result. For interview reasoning, logical processing order is still the right mental model.

Can ORDER BY use a SELECT alias?

Usually yes. ORDER BY happens after SELECT in the logical order, so aliases such as paid_revenue are commonly available there.

Should I use CTEs in interviews?

Yes when they clarify the query. A CTE is especially useful when filtering on an alias, window function, or intermediate result. Keep names specific so the interviewer can follow your reasoning.