Department Summary Table — CREATE TABLE AS in SQL

The problem

Helix Systems' HR dashboard materializes headcount and earliest-hire metrics per department into a temp table to support multiple workforce reports in the same session.

Write a query to return the department ID, employee count, and earliest hire date for each department_id value.

Assumptions:

The employees table has one row per employee with a department_id and a hire_date.
Each department_id value present in employees should appear once in the result.
For each department, the employee count is the number of employees in that department_id. The earliest hire date is the smallest hire_date across those employees.

Output:

One row per department, with columns department_id, employee_count, and earliest_hire.

Schema · hr 4 tables

departments

id integer

name text

location text

budget numeric

salaries

id integer

employee_id integer

amount numeric

effective_date date

end_date? date

employees

id integer

name text

email text

department_id integer

manager_id? integer

hire_date date

title text

is_active boolean

job_history

id integer

employee_id integer

title text

department_id integer

start_date date

end_date? date

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  department_id,
  COUNT(*) AS employee_count,
  MIN(hire_date) AS earliest_hire
FROM
  employees
GROUP BY
  department_id

The shape

Group employees by department_id, then count the rows and take the smallest hire date inside each group. The result is one row per department with the headcount and earliest hire already computed, which is the per-category summary worth caching in a temp table for the HR dashboard.

Clause by clause

SELECT department_id, COUNT(*) AS employee_count, MIN(hire_date) AS earliest_hire returns three columns. department_id labels each group; COUNT(*) counts the employees in that department (department 1 reports 17); MIN(hire_date) returns the smallest hire date among them, which is the earliest one.
FROM employees reads every employee record.
GROUP BY department_id partitions the rows by department so the aggregates run once per department. Without it, COUNT(*) would return one number for the whole company and MIN(hire_date) would return the single earliest hire across all of Helix.

Why MIN on a date

MIN and MAX work on any orderable type, not only on numbers. PostgreSQL knows how to compare two hire_date values, so MIN(hire_date) returns the earliest one in each group the same way MIN(salary) would return the lowest salary. "Earliest" and "smallest" are the same operation when the underlying type is a date.

You practiced a per-category aggregation that pairs a count with a MIN over a date — the kind of compact summary worth caching in a temp table for repeat use.

Return the department ID, employee count, and earliest hire date for each `department_id` value

The shape

Clause by clause

Why MIN on a date

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.