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.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.