Intercom – Frequency of the Number of Support Employees Involved in Conversations

Question

Which are the percentages of how many customer support employees were involved in a conversation?

Schema

Intercom

Output

 

Intercom – Frequency of the Number of Support Employees Involved in ConversationsSQL Query

SELECT number_of_assignees AS customer_success_heroes,
       count(number_of_assignees) AS Frequency,
       count(number_of_assignees) * 100. /
  (SELECT count(DISTINCT conversation_id)
   FROM conversations_parts
   WHERE author_type = 'admin') AS Percentage
FROM
  (SELECT count(DISTINCT author_id) AS number_of_assignees,
                                       conversation_id
   FROM conversations_parts
   WHERE author_type = 'admin'
   GROUP BY conversation_id) assignees_nums
GROUP BY number_of_assignees
ORDER BY count(number_of_assignees) DESC

 

in Customer Support