Department Spend Difference

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

Leadership wants to compare peak metric values between engineering and marketing. Find the highest metric value recorded in each of the two departments, then return the absolute gap between them as a single number.

The compensation gap between departments.

Tables

employee_metrics

Columns: metric_id INTEGER, department TEXT, metric_name TEXT, metric_value REAL, fiscal_quarter TEXT, fiscal_year INTEGER

Expected output

Worked solution and explanation

Strip the costume off this one and it is a two-scalar subtraction wrapped in an absolute value. The business asks for a 'gap between departments,' but the real skill is computing two independent aggregates and folding them into one result row without assuming which side is bigger. Everyone reaches for MAX. What separates a clean answer is keeping the two department filters isolated, resisting GROUP BY when the output is a single number, and refusing the bare subtraction: on this data Marketing's peak beats Engineering's, so Engineering minus Marketing comes back negative.

The trap: GROUP BY when the answer is one row

The instinct on anything that says 'each department' is GROUP BY department. Do that here and you get two rows back, one per department, and then you are stuck trying to subtract one row from another, which SQL does not do the way you want. The output is a single scalar: the absolute difference. The moment you see one number in the expected preview, that is your signal to compute each side as its own scalar subquery, not as grouped rows.

Step 1: Get the peak for Engineering as a scalar

A subquery that filters to department = 'Engineering' and applies MAX(metric_value) returns exactly one value. Because it produces a single row and a single column, SQL treats it as a scalar you can drop into an arithmetic expression. No grouping needed: the WHERE already pins you to one department.

Step 2: Do the same for Marketing

Write an identical scalar subquery with department = 'Marketing'. Keeping the two filters in separate subqueries is what guarantees the departments never bleed into each other. This is the whole game: two clean, independent peaks.

Step 3: Subtract and wrap in ABS

Subtract the two scalars and wrap the result in ABS(). On this seed Engineering peaks at 92.0 and Marketing at 94.9, so a bare Engineering minus Marketing returns -2.9. ABS() folds the sign away, so you never have to reason about which department is larger. That is why the prompt says 'absolute gap': it frees you from a CASE expression or a conditional swap.

SELECT ABS(
  (SELECT MAX(metric_value) FROM employee_metrics WHERE department = 'Engineering')
  - (SELECT MAX(metric_value) FROM employee_metrics WHERE department = 'Marketing')
) AS absolute_difference

Overbuilt (self-join)

Put GROUP BY department into a CTE, then self-join the Engineering row to the Marketing row on a constant to subtract. It works, but it is three times the code and invites a join-condition mistake.

Clean (scalar subqueries)

Two scalar subqueries subtracted inside ABS. Each subquery scans for one department's MAX independently. It reads top to bottom, with no join reasoning required.

Common follow-up questions

• What should the result be if one of the two departments has no rows at all? (Tests whether the candidate knows `MAX` over an empty filter returns `NULL`, which propagates through the subtraction and makes `ABS` return `NULL`. They should propose `COALESCE` or a documented assumption.)
• Now return the gap for every pair of departments, not just Engineering and Marketing. (Forces a generalization: a self-join of per-department maxes or a window approach. It reveals whether they understand why the two-subquery trick was specific to a fixed pair.)
• How would the query change if you only wanted the peak within a single `fiscal_year`? (Checks that they can extend each `WHERE` with an additional predicate without breaking the scalar shape.)