# The Named Transaction

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

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

The finance team's line-item report only shows product IDs. Enrich each transaction with the product name from the catalog so stakeholders can see what was actually purchased.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a fundamental JOIN problem. Interviewers use it to verify that you understand how to enrich fact rows with dimension attributes using a foreign key relationship.

---

### Break down the requirements

#### Step 1: Join transactions to products

`JOIN products ON transactions.product_id = products.product_id` enriches each transaction row with the product name from the catalog.

#### Step 2: Select the required columns

Return `transaction_id`, `user_id`, `product_id`, `quantity`, `transaction_date`, and `product_name` to show the enriched line items.

---

### 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
JOIN products p ON t.product_id = p.product_id
```

> **Cost Analysis**
>
> The join matches 50M transactions against 20K products. With an index on `products(product_id)`, each transaction lookup is O(1). The output has the same row count as transactions (no fan-out since product_id is a primary key in products).

> **Interviewers Watch For**
>
> Whether you use INNER JOIN vs LEFT JOIN. If every transaction must have a valid product, INNER JOIN is correct. LEFT JOIN would include transactions with missing product references.

> **Common Pitfall**
>
> Using `SELECT *` instead of listing specific columns. In production, this pulls unnecessary data 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 use LEFT JOIN to preserve orphan transactions.)_
- How would you also include the product category? _(Add p.category to the SELECT list; no additional join 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/transactions_with_product_names)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.