I am trying to figure out what to put in the WHERE clause that will return the m.sender id, a count of m.receiver id's that are NOT in the m.sender's department, and the department name that m.sender is in.
For example, if the m.sender id is in 'Sales', the count of m.receiver ids will be from all departments except 'Sales'.
I am trying to find a list of m.sender ids that have the highest out of department communication.
(Using Google BigQuery)
SELECT DISTINCT m.sender, COUNT(DISTINCT m.receiver) AS messages_received, e.department
FROM collaboration.messages as m
JOIN collaboration.employees as e
ON m.sender = e.id
WHERE ?
GROUP BY m.sender, e.department
ORDER BY messages_received DESC
LIMIT 5;
| messages | messages sent and received |
|---|---|
| sender | represents the employee id of the employee sending the message. |
| receiver | represents the employee id of the employee receiving the message. |
| employees | information on each employee |
|---|---|
| id | represents the employee id of the employee |
| department | is the department within the company. |
CodePudding user response：
If I understand correctly this could be what you need:
SELECT DISTINCT m.sender, COUNT(DISTINCT m.receiver) AS messages_received, e.department
FROM collaboration.messages as m
JOIN collaboration.employees as e
ON m.sender = e.id
LEFT JOIN collaboration.employees as receiver_dep
ON m.receiver = receiver_dep.id
WHERE receiver_dep.department <> e.department
GROUP BY m.sender, e.department
ORDER BY messages_received DESC
LIMIT 5;
You need an extra join to obtain the receiver's department and check if it is different from the sender's.