Per-Group Aggregates Without GROUP BY

A staple interview ask: "Show every row alongside an aggregate of its group." For example, list each employee with their department's maximum salary on the same line.

A plain GROUP BY collapses rows, so it cannot keep the per-employee detail. You need the detail rows and a group-level number together.

A correlated subquery solves this elegantly: it computes the group aggregate for each detail row without collapsing anything.

If you write SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, you get one row per department, losing individual names.

Adding name to the SELECT without adding it to GROUP BY raises the classic "column must appear in GROUP BY" error.

The interviewer is checking whether you understand that GROUP BY reduces cardinality. To keep detail rows, you compute the aggregate a different way.