Is there any postgreSQL function to calculate mathematical metrics under certain conditions

userCjJl1 0
Viewed 41
Call_ID UUID Intent_Product
A 123 Loan_BankAccount
A 234 StopCheque
A 123 request_agent
B 900 Loan_BankAccount
B 123 request_agent

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
database sql postgresql
1 Answers
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'
intent_product resolved_count containment_count
Loan_BankAccount 2 0.80
StopCheque 1 0.40

Fiddle

DannySlor0
Related