Intercom – Min, Max and Average Resolution Time per Employee

Question

What is the average time it takes for each employee to solve an issue? How can we measure performance?

Schema

Intercom

Output

 

Intercom – Min, Max and Average Resolution Time per Employee

SQL Query

WITH durations AS
  (SELECT assignee_id,
          extract(epoch
                  FROM updated_at - created_at) / (60*60) AS duration_to_solution
   FROM conversations
   WHERE OPEN = FALSE),
     assignee_names AS
  (SELECT id,
          name
   FROM admins)
SELECT name,
       min(duration_to_solution) AS MIN,
       max(duration_to_solution) AS MAX,
       avg(duration_to_solution) AS average
FROM durations
JOIN assignee_names ON (durations.assignee_id = assignee_names.id)
GROUP BY name

 

in Customer Support