0
$\begingroup$

I have the following data in a table where I want to calculate the average time between 1st and 2nd call.

I know how to get the average, but I have a tough time figuring out how to subtract the 2nd from 1st attempt since it is in the same column. I am more familiar with subtracting things between columns.

enter image description here

$\endgroup$
0
$\begingroup$

If you had a table containing only the first call details and another table containing only those of the second call you'd be able to join them, right? Then the two times would be in separate columns. You don't have to copy data or split the table to achieve this. It can be done logically by referencing the table twice in the query and using a self-join.

select
    AVG(second_call.time_of_call - first_call.time_of_call)
from TheTable as first_call
inner join TheTable as second_call
    on first_call.customer_id = second_call.customer_id
where first_call.call_attempt = 1
and second_call.call_attempt = 2;

Logically I split the table into two, one called first_call and one called second_call. The former only contains rows for call_attempt 1 and the latter for call attempt 2. This is enforced in the WHERE clause. I'm assuming each customer will only ever have one row for each call attempt number.

Then I join these tables on the customer_id. I use an inner join so only customers that have both first second calls will be processed. It would be a good idea to profile the data to see how this affects the quality of the calculation.

You'll likely want to change the subtraction inside the AVG() to get the format you need.

If run-time performance is critical it may be more run-time efficient to write this using window functions rather than a self-join, depending on what indexes exist and the cardinalities.

| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.