The Spending Floor

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
L3
Asked in
Shopify

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.

Everyone has a smallest purchase.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

transactions

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

transactions: 10 of 200 rows
transaction_iduser_idproduct_idquantitytotal_amounttransaction_date
10671971001223.462026-02-02
11342941050336.932026-03-03
12013911099450.42026-04-04
12684881148563.872026-05-05
13355851197002026-06-06
14026821246290.812026-07-07
146977912953104.282026-08-08
153687613444117.752026-09-09
160397313935-452026-01-10
1670107014421144.692026-02-11

Expected output

First 5 of 15 rows
user_idusernamemin_amount
973brian-45
585andrew_k0
197aaron4223.46
294 amelia36.93
391arjun 50.4

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
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

Common follow-up questions