Postgres 16

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

CREATE TABLE customer_courier_chat_messages (
  sender_app_type text
, customer_id int
, from_id int
, to_id int
, chat_started_by_message bool
, order_id int
, order_stage text
, courier_id int
, message_sent_time timestamptz
);

SET datestyle = 'DMY';
   
INSERT INTO customer_courier_chat_messages( sender_app_type, customer_id, from_id, to_id, chat_started_by_message, order_id, order_stage, courier_id, message_sent_time)
VALUES
  ('Customer IOS',99,99,21,FALSE,555,'PICKING_UP',21,timestamp '9/8/22 8:02')
, ('Courier IOS',99,21,99,FALSE,555,'ARRIVING',21,timestamp '9/8/22 8:01')
, ('Customer IOS',99,99,21,FALSE,555,'PICKING_UP',21,timestamp '9/8/22 8:00')
, ('Courier Android',122,87,122,TRUE,38,'ADDRESS_DELIVERY',87,timestamp '9/8/22 7:55')
, ('Customer Android',43,43,75,FALSE,875,'PICKING_UP',75,timestamp '7/8/22 14:55')
, ('Courier Android',43,75,43,FALSE,875,'ARRIVING',75,timestamp '7/8/22 14:53')
, ('Customer Android',43,43,75,FALSE,875,'PICKING_UP',75,timestamp '7/8/22 14:51')
, ('Courier Android',43,75,43,TRUE,875,'ADDRESS_DELIVERY',75,timestamp '7/8/22 14:50')
, ('Customer IOS',23,23,21,FALSE,134,'PICKING_UP',21,timestamp '7/8/22 10:02')
, ('Courier IOS',23,21,23,FALSE,134,'ARRIVING',21,timestamp '7/8/22 10:01')
, ('Customer IOS',23,23,21,FALSE,134,'PICKING_UP',21,timestamp '7/8/22 10:00')
RETURNING *;

CREATE TABLE

SET

sender_app_type	customer_id	from_id	to_id	chat_started_by_message	order_id	order_stage	courier_id	message_sent_time
Customer IOS	99	99	21	f	555	PICKING_UP	21	2022-08-09 08:02:00+00
Courier IOS	99	21	99	f	555	ARRIVING	21	2022-08-09 08:01:00+00
Customer IOS	99	99	21	f	555	PICKING_UP	21	2022-08-09 08:00:00+00
Courier Android	122	87	122	t	38	ADDRESS_DELIVERY	87	2022-08-09 07:55:00+00
Customer Android	43	43	75	f	875	PICKING_UP	75	2022-08-07 14:55:00+00
Courier Android	43	75	43	f	875	ARRIVING	75	2022-08-07 14:53:00+00
Customer Android	43	43	75	f	875	PICKING_UP	75	2022-08-07 14:51:00+00
Courier Android	43	75	43	t	875	ADDRESS_DELIVERY	75	2022-08-07 14:50:00+00
Customer IOS	23	23	21	f	134	PICKING_UP	21	2022-08-07 10:02:00+00
Courier IOS	23	21	23	f	134	ARRIVING	21	2022-08-07 10:01:00+00
Customer IOS	23	23	21	f	134	PICKING_UP	21	2022-08-07 10:00:00+00

INSERT 0 11

/*
 first_courier_message: Timestamp of the first courier message
 first_customer_message: Timestamp of the first customer message
 num_messages_courier: Number of messages sent by courier
 num_messages_customer: Number of messages sent by customer
 first_message_by: The first message sender (courier or customer)
 conversation_started_at: Timestamp of the first message in the conversation
*/

SELECT *
     , CASE COALESCE(first_msg_courier , 'infinity')
          < COALESCE(first_msg_customer, 'infinity')
          WHEN true  THEN 'Courier'
          WHEN false THEN 'Customer'  -- ELSE null
       END AS first_msg_by
     , LEAST (first_msg_courier, first_msg_customer) AS conversation_start
FROM  (
   SELECT order_id
        , min(message_sent_time) FILTER (WHERE app_type = 'Co') AS first_msg_courier
        , min(message_sent_time) FILTER (WHERE app_type = 'Cu') AS first_msg_customer
        , count(*) FILTER (WHERE app_type = 'Co') AS num_msg_courier
        , count(*) FILTER (WHERE app_type = 'Cu') AS num_msg_customer        
   FROM  (SELECT order_id, left(sender_app_type, 2) AS app_type, message_sent_time FROM customer_courier_chat_messages) c
   GROUP  BY order_id
   ) sub;

order_id	first_msg_courier	first_msg_customer	num_msg_courier	num_msg_customer	first_msg_by	conversation_start
555	2022-08-09 08:01:00+00	2022-08-09 08:00:00+00	1	2	Customer	2022-08-09 08:00:00+00
875	2022-08-07 14:50:00+00	2022-08-07 14:51:00+00	2	2	Courier	2022-08-07 14:50:00+00
134	2022-08-07 10:01:00+00	2022-08-07 10:00:00+00	1	2	Customer	2022-08-07 10:00:00+00
38	2022-08-09 07:55:00+00	null	1	0	Courier	2022-08-09 07:55:00+00

SELECT 4