Going Once

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

L4

Asked in

TikTok

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. Walk the catalog in product-ID order, from the lowest number up.

The hammer falls. Who bid the most?

Tables

products

Columns: product_id INTEGER, product_name TEXT, in_stock INTEGER, category TEXT, price REAL, rating REAL

transactions

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

Expected output

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, with transaction_id as the tie-breaker for determinism, using ROW_NUMBER().

Step 2: Outer-join so empty products survive

LEFT JOIN the in-stock products 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(t.transaction_id) gives 0 for transaction-less products, MAX(t.total_amount) gives the highest bid, and the rn = 1 user_id is the winner (NULL when no transactions).

Step 4: Order by product id

Order by product_id, lowest first. The key is unique per row, so the order is total.

The solution

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;

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.)