# Going Once

> The hammer falls. Who bid the most?

Canonical URL: <https://datadriven.io/problems/going_once>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The product team is building a sales leaderboard for in-stock items. For each product currently in stock, show how many transactions reference it, the highest transaction amount, and which user placed the top transaction. Products with no transactions should still appear, showing a count of zero and no top user.

## Worked solution and explanation

### What this problem is really about

This is 'fetch the row that owns the aggregate' wearing an auction costume. Anyone can hand you `MAX(total_amount)` per product with a GROUP BY. The skill being probed is returning WHO placed that top transaction in the same pass: MAX gives you the number, not the `user_id` attached to it. A correlated subquery or self-join gets the winner, but it re-scans the entire transactions table once per product. The clean move is ROW_NUMBER() in a CTE to tag each product's top bid once, then an outer join back to it. And there is a trap in the empties: filter the products with an inner join and every in-stock listing that never sold silently disappears; the count has to survive as 0 with a NULL winner.

---

### Break down the requirements

#### Step 1: Rank each product's bids

In ranked_bids, rank transactions within each product_id by total_amount descending (transaction_id tie-breaker for determinism) using ROW_NUMBER().

#### Step 2: Outer-join so empty products survive

LEFT JOIN products (in-stock only) to transactions so products with no transactions are retained, and to ranked_bids restricted to rn = 1 to surface the top-transaction user.

#### Step 3: Aggregate to one row per product

Aggregate per product: COUNT(transaction_id) gives 0 for transaction-less products, MAX(total_amount) gives the highest bid, and the rn=1 user_id is the winner (NULL when no transactions).

#### Step 4: Order for a deterministic result

Order by product_id for a stable, deterministic result.

---

### The solution

**Row-number for auction lot summary**

```sql
WITH ranked_bids AS (
    SELECT
        product_id,
        user_id,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY total_amount DESC, transaction_id) AS rn
    FROM transactions
)
SELECT
    p.product_id,
    p.product_name,
    COUNT(t.transaction_id) AS bid_count,
    MAX(t.total_amount) AS highest_bid,
    rb.user_id AS winner
FROM products p
LEFT JOIN transactions t ON p.product_id = t.product_id
LEFT JOIN ranked_bids rb ON p.product_id = rb.product_id AND rb.rn = 1
WHERE p.in_stock = 1
GROUP BY p.product_id, p.product_name, rb.user_id
ORDER BY p.product_id;
```

> **Cost Analysis**
>
> The ROW_NUMBER() in the CTE ranks the full transactions table before any join or GROUP BY happens, so that partitioned sort is the heaviest step, not the products scan. As bid volume grows, a composite index on transactions(product_id, total_amount DESC) lets the engine feed the window function pre-sorted per partition and skip a full sort.

> **Interviewers Watch For**
>
> The tell of a senior candidate is reaching for ROW_NUMBER to carry the winner's user_id alongside the aggregate, rather than a correlated subquery that re-reads transactions per product. They can also explain why ROW_NUMBER (not RANK) keeps exactly one winner when two bids tie on amount.

> **Common Pitfall**
>
> Using an inner join instead of LEFT JOIN drops every in-stock product that never sold, so the zero-transaction rows vanish from the result and the count silently fails grading.

---

## Common follow-up questions

- If two users place the exact same highest bid on a product, which one does your query report as the winner, and how would you make that choice explicit? _(Tests understanding of tie semantics between ROW_NUMBER, RANK, and DENSE_RANK.)_
- Does the database engine materialize your CTE or inline it? How would you check, and when does it matter? _(Tests understanding of CTE materialization behavior, which varies by engine (PostgreSQL materializes by default before v12).)_
- `transaction_id` is unique for every bid. What index strategy keeps your query from doing a full table scan as the bid volume grows? _(Tests whether the candidate can design indexes for high-cardinality columns and understands selectivity.)_
- If the business definition of `in_stock` changed mid-quarter (e.g., a status value was renamed), how would you handle historical consistency? _(Tests awareness of slowly changing dimensions and backward-compatible query design.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/going_once)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.