I have the above table with me: where "Call_ID" means a call that has been made, "UUID" means a particular query that has been asked in the call, and "Intent_Product" means what is the query about.

The expected output is: Expected_Output

Conditions:

1. Resolved Count :- Count of the total number of queries that has been resolved (here for example "Loan_BankAccount"= 2, "StopCheque" = 1) (here request_agent has to be ignored as those are not resolved)
2. Containment Count :- ((Resolved_Count)x(Total_distinct_calls/total_number_of_turns)) (here for example "Containment_Count" for "Loan_BankAccount" = 2*(2/5) = 0.8

CodePudding user response：

with t2 as 
(select round(1.0*count(distinct call_id)/count(*), 2) dis_by_calls from t)

select  intent_product
        ,count(*)                   as Resolved_Count
        ,count(*)*max(dis_by_calls) as Containment_Count
from     t, t2 
group by intent_product
having   intent_product <> 'request_agent'

Fiddle