133. Small departments

easy robinhood

  • *********************************************************************
  • *********************************************************************************

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


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