Purchase Log

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Tata Consultancy Services

Problem

A purchase only makes our audit log if it came in above fifty dollars, and every such purchase gets its own line. For each of those rows in transactions, show the buyer's username from users next to the amount spent and the purchase date.

Names on receipts, not just IDs.

Tables

users

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

transactions

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

Expected output

Worked solution and explanation

What this really is

This is a lookup join dressed up as an audit log. Everything the log needs already sits in transactions except the one column that makes it readable: the buyer's name lives only in users. Anyone can write WHERE total_amount > 50. What separates candidates is noticing that user_id is not a username and reaching across to users for it. Skip the join and you hand the auditors a column of opaque ids. Read the bar as >= 50 and a purchase of exactly fifty dollars leaks into a report meant to hold only purchases above it.

Building it

Step 1: Fix the grain: one row per purchase

The log lists purchases, so every row of transactions that clears the bar is a row of output. A buyer with a dozen qualifying purchases appears a dozen times. There is nothing to collapse, so no GROUP BY and no DISTINCT.

Step 2: Fetch the name from users

Inner-join users to transactions on user_id. Each transaction has exactly one buyer and user_id is unique in users, so the join attaches a name to each purchase without multiplying rows. Every buyer on this data has a users row, so no purchase falls out either.

Step 3: Apply the threshold strictly

total_amount > 50 keeps the 50.40 purchase and would turn away one at exactly 50.00. The same predicate also clears out the voided 0.00 sale and the -45.00 refund, which sit below the bar under any reading.

SELECT u.username, t.total_amount, t.transaction_date
FROM users u
JOIN transactions t ON u.user_id = t.user_id
WHERE t.total_amount > 50

Single-table shortcut

SELECT user_id, total_amount, transaction_date FROM transactions WHERE total_amount > 50 runs and even returns the right number of rows, but the first column holds numbers like 391 that nobody reading the audit can interpret.

With the lookup

The join swaps that number for username, so the same row reads 'arjun ' with its amount and date: the thing the log was asked for in the first place.

Common follow-up questions

• Some transactions now reference a `user_id` with no row in `users`, and the log must still show them. What changes? (Tests whether the candidate knows an inner join drops unmatched purchases and switches to a left join with a placeholder name.)
• The auditors want each buyer's total spend on purchases above fifty dollars instead of the line-by-line log. How does the query change? (Moves from row-level enrichment to a per-buyer aggregate at a new grain.)
• How would you limit the log to the last 90 days without hardcoding a date? (Tests date arithmetic relative to the current date on `transaction_date`.)