I have the above table where "Call_ID" means a call that has been made, "UUID" is a unique key for a turn in the same call (Suppose Call A can have multiple turns such as 123, 234, 789(here)) and "Intent_Product" refers to the description of the query.

The expected output is :

Conditions :

1. Resolution_Count :- Count of the total number of queries that has been resolved ( Here, for example "Loan_BankAccount" =2 , "StopCheque" = 1) (where "Intent_Product" like "Request_Agent" , have to ignored as those are not resolved)
2. Contained_Turns :- Count the total number of queries that has been contained, but ignore those queries which has "Intent_Product" like "Request_Agent" as the successor. ( example :- here Containment count for "Loan_BankAccount" = 1 and Stop_Cheque" = 0 )
3. Contained_Calls :- This would be equal to (Contained_Turns)/(Resolution_Count)

CodePudding user response：

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

https://dbfiddle.uk/TY7LhjfF