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.

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