An order consists of items.
Each item can have one of the following statuses: placed, delivered, paid, canceled, or returned.
A single order can contain items with different statuses.
For example, three items may be delivered while one is canceled.
Which status is used for filtering
The item status is always checked based on its latest state.
What this means:
For example, an order was placed, then paid, and later delivered.
To find this order, you need to build a filter for orders with the status Delivered, since that’s the most recent status.
“Placed” and “Paid” are historical states, and orders can’t be filtered by those statuses
Which statuses should you use in your filter?
Start with the business goal.
In most cases, you’ll want to include “positive” order statuses — such as Placed, Paid, or Delivered (using the OR condition):
Example 1: how to find customers with paid orders (the most recent order status is Paid)
Example 2: find customers whose orders have all items paid (the most recent order status is Paid)
Difference between the filters:
In the first case, orders with partially canceled or delivered items are included.
In the second case, all the items are paid.