# Cheapest Transaction per User

> Everyone has a smallest purchase.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The pricing team is looking at each user's lowest-value purchase to understand the floor of what people are willing to spend. Show the user ID, username, and their smallest transaction amount, from the lowest up.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests JOIN with GROUP BY and MIN aggregation. It verifies that you can join two tables, aggregate per entity, and sort by the aggregate value.

---

### Break down the requirements

#### Step 1: Join users to transactions

Join on `user_id` to get `username` alongside transaction data.

#### Step 2: Find minimum transaction per user

`GROUP BY u.user_id, u.username` with `MIN(t.total_amount)` gives each user's smallest purchase.

#### Step 3: Sort ascending

`ORDER BY MIN(t.total_amount) ASC` lists from smallest to largest floor price.

---

### The solution

**Join with min aggregation**

```sql
SELECT u.user_id, u.username, MIN(t.total_amount) AS min_amount
FROM users u
JOIN transactions t ON u.user_id = t.user_id
GROUP BY u.user_id, u.username
ORDER BY min_amount ASC
```

> **Cost Analysis**
>
> Hash join of 5M users to 30M transactions, then aggregation to 5M output rows (one per user). The sort on min_amount is the most expensive step at this output cardinality.

> **Common Pitfall**
>
> Using an INNER JOIN drops users with no transactions. If the prompt said "all users," you would need LEFT JOIN with COALESCE. Here the prompt implies only users with purchases matter.

---

## Common follow-up questions

- How would you also show the product name of the cheapest transaction? _(Tests first-per-group pattern: window function or correlated subquery to get the full row.)_
- What if multiple transactions share the minimum amount for a user? _(MIN returns one value; getting all tied rows requires a different approach.)_
- How would you include users with zero transactions? _(Tests LEFT JOIN and NULL handling for users with no matches.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/cheapest_transaction_per_user)
- [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.