Helix Systems' HR compliance team is reviewing salary records for data quality purposes.
Write a query to return every employee's ID, name, total number of salary records on file, and highest salary amount on record.
Assumptions:
- An employee's salary-record count is the number of
salariesrecords linked to thatemployee_id. The highest salary is the largestamountacross those records. - Every employee must appear in the result, including employees with no salary records on file. Employees with no salary records should show a count of
0and a missing highest-salary value.
Output:
- One row per employee, with columns
id,name,salary_count, andmax_salary.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
e.id,
e.name,
sal_stats.salary_count,
sal_stats.max_salary
FROM
employees e
CROSS JOIN LATERAL (
SELECT
COUNT(*) AS salary_count,
MAX(amount) AS max_salary
FROM
salaries s
WHERE
s.employee_id = e.id
) sal_statsThe shape
The lateral runs per employee and reduces that employee's salary records to one stats row. No GROUP BY inside the aggregate means the lateral always returns one row per employee, which is what keeps every employee, including those with no salary records on file, in the compliance review.
Clause by clause
SELECT e.id, e.name, sal_stats.salary_count, sal_stats.max_salaryreturns the employee's identity from the outer table and the two aggregate stats from the lateral.FROM employees eis the driving table. Every row will be paired with exactly one lateral row, so every employee survives to the output.CROSS JOIN LATERAL ( SELECT COUNT(*) AS salary_count, MAX(amount) AS max_salary FROM salaries s WHERE s.employee_id = e.id ) sal_statsruns once per employee. Because the aggregate has noGROUP BY, it returns one row regardless of whether the inner filter matches anything. For an employee with no salary records,COUNT(*)is0andMAX(amount)is NULL, which is the missing max-salary value the prompt asks for.CROSS JOINthen keeps every employee because the lateral always has one row to pair.
Why this and not a scalar correlated subquery in SELECT
A correlated subquery in SELECT can return one scalar, so you could pull salary_count and max_salary as two separate correlated subqueries, one per column. That works on this exact shape. The lateral form computes both aggregates from a single scan of salaries per employee instead of two; on a large salaries table that is the difference between one pass and two. The lateral also keeps related output columns together as a single derived value, which reads more naturally.
The trap
The "missing max-salary" requirement is what forces the aggregate to live inside the lateral with no GROUP BY. Write the lateral as SELECT amount FROM salaries WHERE s.employee_id = e.id and aggregate outside with MAX(sal_stats.amount) and the employees with no salary records vanish, because the lateral returns zero rows and CROSS JOIN drops the outer employee. Aggregate-inside-the-lateral is the shape that preserves every outer row under CROSS JOIN LATERAL.
You practiced CROSS JOIN LATERAL with COUNT and MAX in the inner aggregate — every parent record is preserved; COUNT returns 0 and MAX returns missing when no children match.
