Department Spend Gap

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Chewy

Problem

What is the absolute difference between the largest single transaction made by anyone in Engineering and the largest single transaction made by anyone in Marketing?

Gap between Engineering's and Marketing's biggest single purchase

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

employees

Columns: employee_id INTEGER, emp_name TEXT, department TEXT, salary REAL, manager_id INTEGER, hire_date TEXT, user_id INTEGER

Expected output

Worked solution and explanation

This is two filtered maxima pulled from a table that cannot filter itself, dressed up as a spend comparison. transactions holds every amount and no department, while employees holds the department and no amount, so nothing works until you bridge them on user_id. Everyone gets the join. What separates candidates is one word in the question: absolute. On this data Engineering's biggest purchase is 1222.29 and Marketing's is 1262.70. Subtracting Marketing from Engineering gives -40.41, so a query that drops ABS is graded wrong even though every number inside it is right.

Walking to the answer

Step 1: Bridge amounts to departments on user_id

employees.user_id is the key that matches transactions.user_id. employee_id looks tempting but lands on no purchase from either department. The inner join also drops staff whose user_id is NULL, which is exactly right: no account, no purchases.

Step 2: Take one MAX(total_amount) per department

Each scalar subquery filters the joined rows to one department with WHERE e.department = ... and keeps the single biggest total_amount. The question says single transaction, so you want the MAX of a row, not the SUM of a person's spend. Every user_id sits on two employees rows. That would double a sum, but it leaves a maximum untouched.

Step 3: Subtract, then strip the sign

ABS(eng_max - mkt_max) is the gap whichever side is bigger. Intuition cannot tell you the subtraction order here, because the two maxima sit only 40.41 apart on amounts around 1,200.

The solution

SELECT ABS(
    (SELECT MAX(t.total_amount)
     FROM transactions t
     JOIN employees e ON t.user_id = e.user_id
     WHERE e.department = 'Engineering')
    -
    (SELECT MAX(t.total_amount)
     FROM transactions t
     JOIN employees e ON t.user_id = e.user_id
     WHERE e.department = 'Marketing')
) AS spend_gap

Signed difference

Engineering's max minus Marketing's max returns -40.41 on this data. The join is right and both maxima are right, but the sign makes the answer wrong.

Absolute gap

Wrapping the subtraction in ABS returns 40.41 whichever department is ahead, which is what 'the difference between' means to whoever asked.

Common follow-up questions

• How would you compute this gap for every pair of departments? (Tests computing per-department maxima once and pairing the departments with each other.)
• What if an employee could belong to multiple departments? (Tests whether the schema allows it and how the join grain changes.)
• What if one department has no transactions at all? (That subquery returns NULL and so does the whole `ABS`; tests whether you would default it with `COALESCE`.)