Finding average emails sent per user

user_5278594 0
Viewed 127

I have a table with sample data;table

email_id | sender_id  | received_id |email_cnt
 1      | 101        |  102        | t1
 2      | 101        |  103        |  t2
 3      | 102        |  103        |  t3
 4      | 103        |  104        |  t4

Here email_id is unique identifier. The goal is to find email cnt per user. As we can see, total email_cnt is t1+t2+t3+t4. And we have 4 unique users. Hence in the end, we should be able to get (t1+t2+t3+t4)/4 as the answer.

My idea is to first identify caller who are also receivers.

SELECT tab1.receiver_id,
tab1.email_cnt AS received,
 tab2.sender_id,
 tab2.email_cnt AS made
FROM table tab1
JOIN table tab2
ON tab1.receiver_id=tab2.caller_id;

This gives following output;

receiver_id | received     |sender_id | made
   102      |     t1        |   102   | t3
   103      |    t2         |   103   | t4
   103      |    t3         |  103    | t4

The problem is the value t4 is getting repeated due to the join here. It might lead to over counting the email_cnt later on. Can I kindly get some lead on how to tackle this problem?thanks

postgresql sql
4 Answers

This seems a bit tricky. But you can do:

select max(total_duration) * 1.0 / count(distinct v.id)
from (select t.*, sum(call_duration) over () as total_duration
      from t
     ) t cross join lateral
     (values (caller_id), (receiver_id)) v(id);

Here is a db<>fiddle.

user_11440350

This might work but you might need to do a sum(Made) / count(distinct caller_ID) to get the average value per caller

left join would be useful here and using the following aggregations sum & count

SELECT tab1.receiver_id,
       --tab1.call_duration AS received,
       sum(tab2.call_duration) / count(distinct tab2.caller_id) as Made
FROM table tab1
left JOIN table tab2
ON tab1.receiver_id=tab2.caller_id
group by
      tab1.receiver_id,
      tab1.call_duration;

this would also cause an additional row but comment out duration to get 1 result per customer

user_84026900

To compute the average time spent per customer, you can

select sum(call_duration) / count(distinct receiver_id)
from yourtable;

If I misunderstood the problem, then please let me know.

EDIT

It turns out that the caller and the receiver are both customers. So

select sum(call_duration) / (count(distinct receiver_id) + count(distinct caller_id) - (select count(distinct mt1.caller_id) from mytable mt1 join mytable mt2 on mt1.caller_id = mt2.receiver_id))
from mytable;
user_4365600

I might be misinterpreting but it seems that both receiver and caller are customers and you would like individual call length by the customer regardless of them being a receiver or caller?

I suggest using a Union which will remove the duplicate calls i.e. work on unique calls per customer and then you can average duration

WITH CTE AS (  
SELECT 
call_id,
receiver_id as customer_id, 
call_duration

FROM table 

UNION 

SELECT 
call_id, 
caller_id as customer_id, 
call_duration

FROM table
) 

SELECT 
AVG(call_duration) AS AVG_CallDuration, 
COUNT(call_id) AS TotalCalls, 
customer_id 

FROM CTE

GROUP BY customer_id
user_84856480
Related