MySQL 8.0

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

CREATE TABLE your_table (
  chat_id              INT,
  agent_login          VARCHAR(32),
  chat_date_of_event   TIMESTAMP,
  chat_event           VARCHAR(32),
  rn                   INT
)
INSERT INTO
  your_table
VALUES
(986339, 'jbr', '2024-02-20 17:09:23', 'Agent Engagement Accepted',  1),
(986339, 'jbr', '2024-02-20 17:10:08', 'cust-message',   1), 
(986339, 'jbr', '2024-02-20 17:11:10', 'agent-message',  1), 
(986339, 'jbr', '2024-02-20 17:12:01', 'cust-message',   2), 
(986339, 'jbr', '2024-02-20 17:12:10', 'agent-message',  2), 
(986339, 'jbr', '2024-02-20 17:12:15', 'cust-message',   3), 
(986339, 'jbr', '2024-02-20 17:12:22', 'cust-message',   4), 
(986339, 'jbr', '2024-02-20 17:12:38', 'cust-message',   5), 
(986339, 'jbr', '2024-02-20 17:12:52', 'agent-message',  3), 
(986339, 'jbr', '2024-02-20 17:13:29', 'agent-message',  4), 
(986339, 'jbr', '2024-02-20 17:13:39', 'agent-message',  5), 
(986339, 'jbr', '2024-02-20 17:16:50', 'agent-message',  6), 
(986339, 'jbr', '2024-02-20 17:18:59', 'agent-message',  7) 

Records: 13  Duplicates: 0  Warnings: 0


WITH
  state_changes AS
(
  SELECT
    *,
    SUM(CASE WHEN chat_event = 'Agent Engagement Accepted' THEN 1 ELSE 0 END)
      OVER (
        PARTITION BY chat_id, agent_login
            ORDER BY chat_date_of_event
      )
        AS chat_agent_engagement_id,
    LAG(chat_event)
      OVER (
        PARTITION BY chat_id, agent_login
            ORDER BY chat_date_of_event
      )
        AS previous_chat_event
  FROM
    your_table
  WHERE
    chat_event IN ('Agent Engagement Accepted', 'cust-message', 'agent-message')
),
  lookahead AS
(
  SELECT
    *,
   LEAD(chat_date_of_event)
      OVER (
        PARTITION BY chat_id, agent_login, chat_agent_engagement_id
            ORDER BY chat_date_of_event
      )
        AS next_timestamp
  FROM
    state_changes
  WHERE
        chat_event <> previous_chat_event
    AND chat_agent_engagement_id > 0
)
SELECT
  chat_id,
  agent_login,
  chat_agent_engagement_id,
  chat_date_of_event,
  next_timestamp
FROM
  lookahead
WHERE
      chat_event = 'cust-message'
  AND next_timestamp IS NOT NULL
ORDER BY
  chat_id,
  agent_login,
  chat_date_of_event


chat_id
agent_login
chat_agent_engagement_id
chat_date_of_event
next_timestamp


986339
jbr
1
2024-02-20 17:10:08
2024-02-20 17:11:10

986339
jbr
1
2024-02-20 17:12:01
2024-02-20 17:12:10

986339
jbr
1
2024-02-20 17:12:15
2024-02-20 17:12:52

chat_id	agent_login	chat_agent_engagement_id	chat_date_of_event	next_timestamp
986339	jbr	1	2024-02-20 17:10:08	2024-02-20 17:11:10
986339	jbr	1	2024-02-20 17:12:01	2024-02-20 17:12:10
986339	jbr	1	2024-02-20 17:12:15	2024-02-20 17:12:52