Logical order of a query

The order PostgreSQL logically evaluates clauses in, which decides what each clause can see.

Syntax

FROM / JOIN  →  WHERE  →  GROUP BY  →  HAVING  →  window functions
→  SELECT (expressions, aliases)  →  DISTINCT  →  ORDER BY  →  LIMIT / OFFSET

You write SELECT first, but it is evaluated late. Each step works on the output of the one before:

FROM / JOIN builds the working set of rows.
WHERE filters individual rows (no aggregates, no aliases, no window functions yet).
GROUP BY collapses rows into groups.
HAVING filters groups (aggregates allowed).
Window functions are computed over the remaining rows.
SELECT computes output expressions and assigns aliases.
DISTINCT removes duplicate output rows.
ORDER BY sorts (aliases are visible here).
LIMIT / OFFSET cut the sorted result.

Consequences:

An alias defined in SELECT cannot be used in WHERE, GROUP BY… (PostgreSQL does allow aliases and positions in GROUP BY as an extension, and in ORDER BY).
To filter on a window function result, compute it in a subquery or CTE and filter outside.
The planner may physically execute steps in a different order; the result is as if this order was followed.

Examples

Every clause in one query starter

SELECT category, count(*) AS products, round(avg(price), 2) AS avg_price
FROM products
WHERE stock > 0
GROUP BY category
HAVING count(*) >= 2
ORDER BY avg_price DESC
LIMIT 3;

WHERE drops out-of-stock rows before grouping; HAVING drops small groups; ORDER BY can use the alias avg_price.

Filtering on a window result needs a wrapper starter

SELECT *
FROM (
  SELECT product_name, category, price,
         rank() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
  FROM products
) ranked
WHERE price_rank = 1
ORDER BY category;

The window function is computed after WHERE, so the filter has to live in the outer query.

Pitfalls

Referencing a SELECT alias in WHERE or HAVING.
Filtering on ROW_NUMBER()/RANK() in the same query level's WHERE.
Expecting LIMIT to apply before ORDER BY or before aggregation.