SQLMaxx
Sign in Start free
N061-M3 Tier 5 · Expert · medium hr · Helix Systems

Return each qualifying `employee_id` and their recorded salary `amount`

Part of Query Structure Patterns for Performance in SQL

The problem

Scenario: Helix Systems' HR audit team is reviewing every salary record on file for employees in the Engineering department.

Task: Write a query to return each qualifying employee_id and their recorded salary amount.

Assumptions:

  • An employee can have multiple salary records.
  • The result covers only salary records belonging to employees whose department name is 'Engineering'.

Output:

  • One row per salary record belonging to an Engineering employee.
  • Columns in this order: employee_id, salary.
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
WITH
  engineering_employees AS (
    SELECT
      e.id AS employee_id
    FROM
      employees e
      JOIN departments d ON e.department_id = d.id
    WHERE
      d.name = 'Engineering'
  )
SELECT
  s.employee_id,
  s.amount AS salary
FROM
  salaries s
  JOIN engineering_employees ee ON ee.employee_id = s.employee_id

The shape

Two phases compose: identify the qualifying employees (those in Engineering), then pull every salary record belonging to that set. The CTE captures the qualification step; the main query reads salaries and joins to the CTE to keep only the qualifying employees' rows.

Clause by clause

  • The CTE engineering_employees joins employees to departments on the department key and filters to d.name = 'Engineering'. The projection is just employee_id — that is the only column the main query needs to identify qualifying rows.
  • SELECT s.employee_id, s.amount AS salary projects the requested columns directly from the salary record.
  • FROM salaries s JOIN engineering_employees ee ON ee.employee_id = s.employee_id reads every salary record and keeps only those whose employee appears in the CTE. The CTE is acting as a filter set.

Why this and not a three-way join in one step

Writing the whole query as SELECT s.employee_id, s.amount FROM salaries s JOIN employees e ON e.id = s.employee_id JOIN departments d ON d.id = e.department_id WHERE d.name = 'Engineering' produces the same rows. The CTE shape buys readability and reuse: the "who counts as an Engineering employee" rule is named in one place, and if the main query needed to reference that set a second time, the name is already there. The structural choice the problem is practicing is the separation of the qualification logic from the outer pull.

The trap

An employee can have multiple salary records. The output here is one row per salary record, not one row per employee — so the same employee_id appears in the result for every salary record they have. If a downstream consumer needs one row per employee, the salary records would have to be aggregated first.

You practiced isolating the qualifying employees in an early CTE before pulling their salary records — a shape that keeps the qualification logic separate from the outer lookup.

How you actually get good at SQL

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

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.