Where does QUALIFY fit in the order of execution in BigQuery?

Here's an example of how QUALIFY fits into the order of execution in SQL.

In the BigQuery example below, we want to compute the second-to-last order_updated event for each order.

To do this, we filter to keep only the rows WHERE order_status = 'order_updated'.

We use there rows then to retrieve the event occurring second - sorting decreasingly by event_ts and partitioning by order_id, using QUALIFY.

The output is then ORDER BY the second_to_last_order_update_ts decreasingly.

SELECT
  order_id,
  event_ts AS second_to_last_order_update_ts,
  order_status

FROM input_data

WHERE order_status = 'order_updated'

QUALIFY ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY event_ts DESC) = 2

ORDER BY second_to_last_order_update_ts DESC