Postgres 14

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

CREATE TABLE your_data (
  call_id          CHAR,
  uuid             INT,
  intent_product   VARCHAR(32)
)

CREATE TABLE


INSERT INTO
  your_data
VALUES
  ('A', 123, 'Loan_BankAccount'),
  ('A', 234, 'StopCheque'),
  ('A', 789, 'Request_Agent_phone_number'),
  ('B', 600, 'Loan_BankAccount'),
  ('B', 787, 'Request_Agent_BankAcc')

INSERT 0 5


SELECT * FROM your_data ORDER BY call_id, uuid


call_id
uuid
intent_product


A
123
Loan_BankAccount

A
234
StopCheque

A
789
Request_Agent_phone_number

B
600
Loan_BankAccount

B
787
Request_Agent_BankAcc



SELECT 5


WITH
  successor AS
(
  SELECT
    your_data.*,
    LEAD(intent_product)
      OVER (
        PARTITION BY call_id
            ORDER BY uuid
      )
        AS successor_intent_product
  FROM
    your_data
),
  aggregate AS
(
  SELECT
    intent_product,
    COUNT(*)   AS turns,
    COUNT(CASE WHEN successor_intent_product LIKE 'Request_Agent_%' THEN NULL ELSE 1 END) AS no_request
  FROM
    successor
  WHERE
    intent_product NOT LIKE 'Request_Agent_%'
  GROUP BY
    intent_product
)
SELECT
  *,
  no_request * 1.0 / turns   AS ratio
FROM
  aggregate


intent_product
turns
no_request
ratio


Loan_BankAccount
2
1
0.50000000000000000000

StopCheque
1
0
0.00000000000000000000



SELECT 2

call_id	uuid	intent_product
A	123	Loan_BankAccount
A	234	StopCheque
A	789	Request_Agent_phone_number
B	600	Loan_BankAccount
B	787	Request_Agent_BankAcc

intent_product	turns	no_request	ratio
Loan_BankAccount	2	1	0.50000000000000000000
StopCheque	1	0	0.00000000000000000000