- *********************************************************************
- *********************************************************************************
Table 1: department
Dimensional table for each department: sales, data science, hr, etc.
col_name | col_type ---------------+------------------- id | int name | varchar (20)
Table 2: employee
Employee information, department they are in and their annual salary.
col_name | col_type ---------------+------------------- id | bigint full_name | varchar (100) department_id | int salary | float
Sample results
id | name | count ----+----------------------+------- 1 | Software Engineering | 3 2 | Sales | 4
Solution postgres
SELECT D.id, D.name, COUNT(E.id)
FROM department D
INNER JOIN employee E
ON E.department_id = D.id
GROUP BY D.id, D.name
HAVING COUNT(E.id) < 5
ORDER BY D.id;
Explanation
This query is retrieving data from two tables, "department" and "employee". It is selecting the "id" and "name" columns from the "department" table and counting the "id" column from the "employee" table.
The query is using an inner join to combine the two tables based on the "department_id" column in the "employee" table and the "id" column in the "department" table.
The data is then grouped by the "id" and "name" columns in the "department" table. The query is using the HAVING clause to filter the results and only return departments with less than five employees.
Finally, the results are sorted in ascending order by the "id" column in the "department" table.
Expected results
More Robinhood questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 134 | Expensive departments robinhood | medium |
|
| 135 | Unlucky employees robinhood | easy |
|