Write a query that will display the full name of employees, the name of the city, and the
list of colleagues of this employee.
If the employee works alone in the city, then the word ‘NOBODY’ should be displayed
in the collegues column.
The list of colleagues must be separated by commas. The employee must not be on the list.
IMPORTANT: The list format must be: last_name, last_name, last_name.
Can use regular expressions.
WITH got_colleagues_num AS
(
SELECT e.first_name, e.last_name, l.city, COUNT(*)
OVER (PARTITION BY e.department_id) - 1 AS colleagues_num
FROM hr.employees e
JOIN hr.departments d ON d.department_id = e.department_id
JOIN hr.locations l ON l.location_id = d.location_id
)
SELECT first_name, last_name, city,
CASE
WHEN colleagues_num > 0
THEN TO_CHAR(colleagues_num)
ELSE 'Nobody'
END AS colleagues
FROM hr.got_colleagues_num
ORDER BY last_name
