# The Spending Floor > Everyone has a smallest purchase. Canonical URL: 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 ### What this really is Beneath the pricing-floor story this is aggregation-per-entity: one row per user carrying that user's minimum spend, then sorted by that minimum. Anyone can type MIN and GROUP BY. Two things separate the candidates. First, MIN, not COUNT or SUM: the ask is the single cheapest purchase, so reflexing to a total or a tally answers a different question entirely. Second, the join is an inner join, so a user who never bought anything simply is not there; reach for an outer join and you invent null-priced floors for people who have no floor at all. --- ### Walking it #### Step 1: Attach the username Join on `user_id` so each transaction carries its owner's `username`. An inner join is the whole point here: it silently drops users who never transacted, which is exactly the behavior the floor metric wants. #### Step 2: Collapse to the minimum `GROUP BY u.user_id, u.username` with `MIN(t.total_amount)` collapses each user's many purchases down to their single cheapest one. Every non-aggregated column in the SELECT has to appear in the GROUP BY, which is why `username` rides alongside `user_id`. #### Step 3: Sort cheapest first `ORDER BY min_amount ASC` puts the lowest floors on top, so the cheapest spenders lead the list. --- ### 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 at most 5M output rows (one per purchasing user). The final sort on min_amount is the priciest step at this output cardinality; the join and the group-by both stream. > **Common Pitfall** > > The tempting mistake is an outer join 'to be safe'. That drags in every purchase-less user with a null min_amount, which is not a spending floor, it is the absence of one. Only switch to LEFT JOIN (with COALESCE) if the prompt explicitly asks for all users. --- ## Common follow-up questions - How would you also show the product of the cheapest transaction? _(Tests first-per-group pattern: a window function or correlated subquery to retrieve the full row, since MIN alone gives only the value.)_ - What if several transactions share a user's minimum amount? _(MIN returns one value; surfacing all tied rows requires ranking or a self-join back to the minimum.)_ - How would you include users with zero transactions? _(Tests LEFT JOIN and NULL handling for users with no matching transactions.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_spending_floor) - [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.