User Spend Audit

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

L4

Asked in

Visa

Problem

Given the username 'alice', what's their total spend on Electronics products with a quality rating above 3?

One user. One category. Total spend.

Tables

products

Columns: product_id INTEGER, category TEXT, rating REAL, product_name TEXT, price REAL, in_stock INTEGER

transactions

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

users

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

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This tests a three-table join with multiple filter conditions. Interviewers check whether you can chain joins, apply filters at the right level, and compute an aggregate across the filtered result.

Break down the requirements

Step 1: Join users to transactions to products

users -> transactions -> products chain: match the user by username, their transactions, and the products in each transaction.

Step 2: Filter by username, category, and rating

WHERE u.username = 'alice' AND p.category = 'Electronics' AND p.rating > 3 applies all three conditions.

Step 3: Sum total spend

SUM(t.total_amount) computes the filtered total spend.

The solution

SELECT SUM(t.total_amount) AS total_spend
FROM users u
JOIN transactions t ON u.user_id = t.user_id
JOIN products p ON t.product_id = p.product_id
WHERE u.username = 'alice'
  AND p.category = 'Electronics'
  AND p.rating > 3

Common follow-up questions

• What if there are multiple users named 'alice'? (Tests whether to filter by `user_id` instead, or add additional disambiguation.)
• How would you show the spend breakdown by product? (Remove the SUM, add GROUP BY `p.product_name`, and show individual product totals.)
• What if SUM returns NULL because no transactions match? (Use COALESCE(SUM(`t.total_amount`), 0) to return 0 instead of NULL.)