Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE orders (
  order_id text
, status_update text
, status_date date
);

INSERT INTO orders VALUES
  ('A','Received'  ,'2020-01-01')  -- org. sample data
, ('A','Pending'   ,'2020-01-15')
, ('A','Processing','2020-01-07')
, ('A','Delivered' ,'2020-01-15')
, ('B','Received'  ,'2020-02-01')
, ('C','Received'  ,'2020-02-15')
, ('C','Delivered' ,'2020-02-20')
, ('D','Received'  ,'2020-05-01')
, ('D','Delivered' ,'2020-05-12')

, ('E','Received'  ,'2019-12-21')  -- case with ealier 'Received'
, ('E','Pending'   ,'2019-12-28')
, ('E','Processing','2020-01-07')
, ('E','Delivered' ,'2020-01-15')

, ('F','Received'  ,'2020-02-01')  -- case with later 'Delivered'
, ('F','Pending'   ,'2020-02-03')
, ('F','Processing','2020-02-07')
, ('F','Delivered' ,'2020-06-15')

, ('G','Received'  ,'2019-12-21')  -- case with ealier 'Received' and later 'Delivered'
, ('G','Pending'   ,'2019-12-28')
, ('G','Processing','2020-01-07')
, ('G','Delivered' ,'2020-06-15')
;
21 rows affected

-- erwin 1 - include rows before 2020-01-01, as long as the delivery date is in the time frame
SELECT *
FROM   orders o
WHERE  EXISTS (
   SELECT FROM orders o1
   WHERE  o1.order_id = o.order_id
   AND    o1.status_update = 'Delivered'
   AND    o1.status_date BETWEEN '2020-01-15' AND '2020-04-25'
   )
ORDER  BY order_id, status_date;


order_id
status_update
status_date


A
Received
2020-01-01

A
Processing
2020-01-07

A
Pending
2020-01-15

A
Delivered
2020-01-15

C
Received
2020-02-15

C
Delivered
2020-02-20

E
Received
2019-12-21

E
Pending
2019-12-28

E
Processing
2020-01-07

E
Delivered
2020-01-15


-- erwin 2 - cut off earlier rows (even if delivered within time frame)
SELECT *
FROM   orders o
WHERE  EXISTS (
   SELECT FROM orders o1
   WHERE  o1.order_id = o.order_id
   AND    o1.status_update = 'Delivered'
   AND    o1.status_date BETWEEN '2020-01-15' AND '2020-04-25'
   )
AND    o.status_date >= '2020-01-15'  -- same as start of time frame - optionally any other cut-off date
ORDER  BY order_id, status_date;


order_id
status_update
status_date


A
Pending
2020-01-15

A
Delivered
2020-01-15

C
Received
2020-02-15

C
Delivered
2020-02-20

E
Delivered
2020-01-15

order_id	status_update	status_date
A	Received	2020-01-01
A	Processing	2020-01-07
A	Pending	2020-01-15
A	Delivered	2020-01-15
C	Received	2020-02-15
C	Delivered	2020-02-20
E	Received	2019-12-21
E	Pending	2019-12-28
E	Processing	2020-01-07
E	Delivered	2020-01-15