Highest Unmatched Sealed Bid

A charity runs a sealed-bid auction with an unusual rule: if two or more bids have exactly the same amount, all of those bids are void. The winner is the highest amount that was bid exactly once.

Return a single row with one column winning_amount. If no amount was bid exactly once (or there are no bids), return that row with NULL.

Tables

sealed_bids — One row per sealed bid submitted.

Example

sealed_bids

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Aggregation & Grouping · problem 10 of 70 · SQLite

The idea

Split the problem in two. The inner query groups bids by amount and keeps the groups of size one with HAVING COUNT(*) = 1; these are the amounts nobody matched.

The outer query applies MAX to that list. An aggregate without GROUP BY always returns exactly one row, and MAX over an empty set is NULL, so the “no winner” case needs no special code.

Sorting the unmatched amounts and taking LIMIT 1 finds the same value when it exists, but returns zero rows when it does not. Wrapping it in a scalar subquery, SELECT (…) AS winning_amount, restores the single NULL row. The alternative does that, and also finds unmatched bids differently: a bid is unmatched when NOT EXISTS another bid with the same amount and a different id.

Solution

SELECT MAX(amount) AS winning_amount
FROM (
  SELECT amount
  FROM sealed_bids
  GROUP BY amount
  HAVING COUNT(*) = 1
) AS unmatched;

Another way

SELECT (
  SELECT b.amount
  FROM sealed_bids AS b
  WHERE NOT EXISTS (
    SELECT 1
    FROM sealed_bids AS o
    WHERE o.amount = b.amount
      AND o.bid_id <> b.bid_id
  )
  ORDER BY b.amount DESC
  LIMIT 1
) AS winning_amount;

Why common answers fail

Takes the highest bid without checking whether another bid matched it.

SELECT MAX(amount) AS winning_amount
FROM sealed_bids;

Correct when a winner exists, but returns no row at all instead of a NULL row when every amount is matched.

SELECT amount AS winning_amount
FROM sealed_bids
GROUP BY amount
HAVING COUNT(*) = 1
ORDER BY amount DESC
LIMIT 1;

What the interviewer is checking

Tests filtering on group size with HAVING and returning a well-defined single row when nothing qualifies.

Mistakes to avoid

• Taking MAX of all amounts.
• Returning an empty result instead of NULL.
• Using WHERE COUNT(*) = 1, which is not allowed; aggregates need HAVING.

Follow-ups to expect

• Return the winning bidder’s name too (join back on amount, still one row).
• Lowest unique bid instead (MIN).
• Run it per auction lot (GROUP BY lot_id, amount, then MAX per lot).

In real work

Unique-bid auctions and finding values that occur exactly once, such as one-off error codes or singleton SKUs, use this group-then-filter pattern.

Practice next