How to perform aggregation without considering certain value from a column and calculating mathematical metrics under certain condition on that

Prabuddha Dasgupta 0
Viewed 47
Call_ID UUID Intent_Product
A 123 Loan_BankAccount
A 234 StopCheque
A 789 Request_Agent_phone_number
B 900 Loan_BankAccount
B 787 Request_Agent_BankAcc

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 :

Intent_Product Resolved_Count Contained_Turns Contained_Calls
Loan_BankAcc 2 1 0.5
Stop_Cheque 1 0 0

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)
database sql postgresql
1 Answers
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

MatBailie0
Related