Intercom – Number of Different Customers per Customer Support Employee

Question

How many different customers each customer support employee has engaged with?

Schema

Intercom

Output

 

Intercom – Number of Different Customers per Customer Support EmployeeSQL Query

SELECT name AS customer_success_hero,
 number_of_distinct_users
FROM
 (SELECT DISTINCT admins.author_id,
 count(DISTINCT users.author_id) AS number_of_distinct_users
 FROM
 (SELECT DISTINCT author_id,
 conversation_id
 FROM conversations_parts
 WHERE author_type = 'admin') admins
 INNER JOIN
 (SELECT DISTINCT author_id,
 conversation_id
 FROM conversations_parts
 WHERE author_type != 'admin') users ON (admins.conversation_id = users.conversation_id)
 GROUP BY admins.author_id) raw_results
JOIN admins ON (raw_results.author_id = admins.id)
ORDER BY number_of_distinct_users DESC

in Customer Support