Skipping Order if Older order is present

I need help with item # 3.

1. Skip duplicate orders
2. Skip cancelled orders
3. Skip order if an older order is present

In the given data, we need to:

• skip id # 2,
• skip id # 3 or 4.
• skip id # 5 (as it repeated in id #6, where we have previous_order_id of 5).

CREATE OR REPLACE TABLE customer_orders (
    id                NUMBER,
    order_date        DATE,
    customer_id       VARCHAR,
    order_id          VARCHAR,
    item_id           VARCHAR,
    order_status      VARCHAR,
    previous_order_id VARCHAR
);
INSERT INTO customer_orders
VALUES
    (1, '2023-07-01', 'customer_id_001', 'order_id_c001_101', 'tv',      'completed', null),
    (2, '2023-07-02', 'customer_id_001', 'order_id_c001_102', 'clothes', 'cancelled', null),
    (3, '2023-07-03', 'customer_id_002', 'order_id_c002_201', 'food',    'completed', null),
    (4, '2023-07-03', 'customer_id_002', 'order_id_c002_201', 'food',    'completed', null),
    (5, '2023-07-04', 'customer_id_002', 'order_id_c002_202', 'dvd',     'completed', null),
    (6, '2023-07-05', 'customer_id_002', 'order_id_c002_203', 'dvd',     'completed', 'order_id_c002_202');

Here is my work in progress:

WITH cte_customer_orders AS (
  SELECT *
    , row_number() OVER (PARTITION BY customer_id, order_id, item_id ORDER BY order_date DESC) AS duplicates
  FROM customer_orders  
)
SELECT * FROM cte_customer_orders
WHERE duplicates=1
  AND order_status='completed'
ORDER BY 1,2,3,4;

I need to skip id # 5.

There are a few other business rules, but I skipped them to focus on this issue only.

I edited your query, it is working.

WITH cte_customer_orders AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_id, order_id, item_id ORDER BY order_date DESC) AS rn
  FROM customer_orders
),
filtered_orders AS (
  SELECT *
  FROM cte_customer_orders
  WHERE rn = 1
    AND order_status = 'completed'
)
-- Select the records according to the specified rules
SELECT *
FROM filtered_orders fo
WHERE NOT EXISTS (
  SELECT 1
  FROM filtered_orders fo2
  WHERE fo.customer_id = fo2.customer_id
    AND fo2.previous_order_id = fo.order_id
)
ORDER BY id;

here is the output.