The Named Transaction

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

L4

Asked in

Clear Capital

Problem

The finance team's line-item report only shows product IDs. Enrich each transaction with the product name from the catalog and walk the report down the ledger in transaction-id order, lowest first, so stakeholders can see what was actually purchased.

Transaction IDs are useless without context. Bring in the product names.

Tables

transactions

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

products

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

Expected output

Worked solution and explanation

What this really is

Strip off the finance framing and this is a fact-to-dimension lookup: each transactions row carries an opaque product_id, and the only place a human-readable name lives is the products catalog. Nothing you compute from the transaction alone produces that name, so the whole problem is the join. The trap is the two decisions wrapped around it: matching on the right key, and choosing INNER JOIN over LEFT JOIN. Match on the wrong column, or drop the condition, and you get a cartesian product that pairs every transaction with every product. Reach for LEFT JOIN and the 18 transactions whose product_id is NULL come back with a null product_name, a line a finance report cannot explain. Knowing which rows you want, and why, is the tell.

Break down the requirements

Step 1: Join transactions to products on product_id

The name lives only in products, so each transaction reaches across to the catalog on the shared product_id. That key is the primary key of products, so each transaction matches at most one catalog row and nothing fans out.

Step 2: Keep only nameable purchases

An INNER JOIN keeps a transaction only when its product_id finds a catalog row. A NULL key never equals anything, so those 18 rows fall away and the report lists 182 purchases, every one with a name.

Step 3: Project the line item and sort the ledger

Select the transaction columns plus p.product_name, leave total_amount out, and sort by transaction_id ascending so the report reads down the ledger.

The solution

SELECT
    t.transaction_id,
    t.user_id,
    t.product_id,
    t.quantity,
    t.transaction_date,
    p.product_name
FROM transactions t
INNER JOIN products p ON t.product_id = p.product_id
ORDER BY t.transaction_id;

Common follow-up questions

• What if some transactions reference products that no longer exist in the catalog? (Tests when to switch to `LEFT JOIN` to preserve orphan transactions.)
• How would you also include the product category? (Add `p.category` to the select list; no additional join is needed.)
• What if transactions had millions of distinct product IDs not in products? (Tests data quality awareness and referential integrity enforcement.)