# The Named Transaction > Transaction IDs are useless without context. Bring in the product names. Canonical URL: 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. ## Tables ### transactions - transaction_id INTEGER - user_id INTEGER - product_id INTEGER - quantity INTEGER - total_amount REAL - transaction_date TEXT Sample rows (10 of 200): | transaction_id | user_id | product_id | quantity | total_amount | transaction_date | | --- | --- | --- | --- | --- | --- | | 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 | | 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 | | 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 | | 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 | | 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 | | 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 | | 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 | | 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 | | 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 | | 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 | ### products - product_id INTEGER - product_name TEXT - category TEXT - price REAL - rating REAL - in_stock INTEGER Sample rows (10 of 200): | product_id | product_name | category | price | rating | in_stock | | --- | --- | --- | --- | --- | --- | | 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 | | 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 | | 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 | | 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 | | 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 | | 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 | | 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 | | 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 | | 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 | | 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 | ## Expected output First 5 of 182 rows: | transaction_id | user_id | product_id | quantity | transaction_date | product_name | | --- | --- | --- | --- | --- | --- | | 1067 | 197 | 1001 | 2 | 2026-02-02 | Basic Device 1X | | 1134 | 294 | 1050 | 3 | 2026-03-03 | Deluxe Bundle 2X | | 1201 | 391 | 1099 | 4 | 2026-04-04 | Pro Unit 3X | | 1268 | 488 | 1148 | 5 | 2026-05-05 | Ultra Tool 4X | | 1335 | 585 | 1197 | 0 | 2026-06-06 | Essential Set 5X | ## 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 **Dimension lookup via inner join** ```sql 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; ``` > **A lookup against a primary key never multiplies rows** > > 50M transactions against 20K products is a textbook broadcast or hash join: the small `products` side fits in memory and each transaction probes it once. Because `product_id` is unique in `products`, the output can never exceed the `transactions` row count; here it is smaller, since unmatched rows drop. > **Say why it is INNER, out loud** > > Interviewers want to hear you choose between `INNER JOIN` and `LEFT JOIN` on purpose. A report that only lists purchases it can name wants `INNER`; `LEFT` would keep the NULL-key transactions and paint their `product_name` null. > **`SELECT *` drags the catalog along** > > Writing `SELECT *` duplicates `product_id` from both tables, pulls unused catalog columns (`price`, `rating`, `in_stock`) plus `total_amount`, and makes the query fragile to schema changes. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_named_transaction) - [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.