Spending Tiers

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

Problem

Label each customer 'high', 'medium' or 'low' by comparing their average transaction amount in our transactions table with the average across every transaction, which is how we bucket our loyalty program. A customer is 'high' only when their average runs more than 5 percent above that overall figure and 'low' only when it runs more than 5 percent below; everyone else is 'medium'.

High rollers, mid-spenders, and the frugal. Everyone gets a tier.

Tables

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 problem is really asking

Strip the loyalty-program costume and this is relative benchmarking at two grains: each customer's own average order size measured against one population-wide average. Anyone can write AVG(total_amount) per user_id. What separates candidates is seeing that the tier boundary is itself a computed number, which has to arrive as a single value and get multiplied into bands. On this data every customer's average sits within about 9 percent of the overall figure near 689, so a hardcoded dollar cutoff drops everyone into one bucket, and tiering raw transactions hands one customer several contradictory labels.

Walking through it

Step 1: Collapse transactions to one average per user_id

Group transactions by user_id and take AVG(total_amount) as avg_spend. This is the customer grain: one row per customer carrying their typical order size, whether they placed twelve orders or fourteen. Averaging here, not summing, is what makes customers with different order counts comparable.

Step 2: Compute the benchmark as a single value

The tier line is the average over every transaction, one number. The scalar subquery (SELECT AVG(total_amount) FROM transactions) delivers it, so each customer is measured against the same population baseline. Refunds and voided sales stay in, exactly as they stay in each customer's own average.

Step 3: Bucket with a CASE over the bands

Multiply the benchmark by 1.05 and 0.95 to draw the bands, then a three-branch CASE labels each customer. Test high first, low next, and let everything else fall through to 'medium', so a customer exactly on either line stays 'medium'. On the seed that splits 15 customers into 3 'high', 2 'low' and 10 'medium'.

The solution

WITH user_avg AS (
    SELECT user_id, AVG(total_amount) AS avg_spend
    FROM transactions
    GROUP BY user_id
)
SELECT
    user_id,
    CASE
        WHEN avg_spend > (SELECT AVG(total_amount) FROM transactions) * 1.05 THEN 'high'
        WHEN avg_spend < (SELECT AVG(total_amount) FROM transactions) * 0.95 THEN 'low'
        ELSE 'medium'
    END AS tier
FROM user_avg

Common follow-up questions

• How would you turn the fixed plus/minus 5 percent bands into quartiles so the tiers stay balanced as spend drifts over time? (Tests whether they can swap relative bands for distribution-based cutoffs (`NTILE` or percentiles).)
• The overall average is referenced twice inside the `CASE`. How would you guarantee the engine evaluates it only once? (Probes scalar-subquery evaluation versus lifting the benchmark into its own CTE.)
• What happens to a customer whose only transactions are refunds, so their average is negative? (Tests reasoning about sign and whether negative averages belong in 'low' or need separate handling.)