Intercom – Number of Open Assigned Conversations per Customer Support Employee

Intercom – Number of Open Assigned Conversations per Customer Support Employee

Question

How many open assigned conversations in total has each one of our customer support heroes?

Schema

Intercom

Output

SQL Query

SELECT DISTINCT name AS customer_success_hero,
                count(DISTINCT msgs.id)
FROM
  (SELECT assignee_id,
          id
   FROM conversations
   WHERE OPEN = TRUE) msgs
INNER JOIN admins ON (msgs.assignee_id = intercom_n_admins.id)
GROUP BY name