# Spending Tiers

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

Canonical URL: <https://datadriven.io/problems/spending_tiers>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The loyalty program buckets customers by how their typical spend compares to everyone else's. For each user, take their average transaction amount and compare it to the overall average across all transactions: label them 'high' when it runs more than 20 percent above that overall average, 'low' when it runs more than 20 percent below, and 'medium' otherwise. Return the user_id and their tier.

## Tables

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 5 of 15 rows:

| user_id | tier |
| --- | --- |
| 100 | medium |
| 197 | medium |
| 294 | medium |
| 391 | medium |
| 488 | medium |

## Worked solution and explanation

### What this problem is really asking

Strip the loyalty-program costume and this is a relative benchmarking problem: every user is scored not against a fixed dollar line but against the crowd. The real skill is holding two grains at once, each user's own average order size and the single population-wide average, then comparing them. Anyone can write AVG grouped by user. The move that separates people is realizing the tier boundary is itself a computed number, the overall average, which has to arrive as a scalar and get multiplied into bands. Reach for hardcoded cutoffs like 200 and 500 instead, and on real spend data almost everyone lands in one bucket and the tier column tells you nothing.

---

### Walking through it

#### Step 1: Collapse transactions to one average per user

Group `transactions` by `user_id` and take AVG(`total_amount`). This is the per-user grain: one row per customer carrying their typical order size, independent of how many orders they placed. Averaging here, not summing, is what makes a one-order user and a hundred-order user comparable.

#### Step 2: Compute the benchmark as a scalar

The tier line is the overall average across every transaction, a single number. A scalar subquery, (SELECT AVG(`total_amount`) FROM transactions), delivers it so each user's average is measured against the same population baseline. This is the piece a hardcoded threshold can never replace.

#### Step 3: Bucket with a CASE over the bands

Multiply the benchmark by 1.2 and 0.8 to draw the high and low bands, then a three-branch CASE labels each user. Order the branches: test the high band first, the low band next, and let everything in between fall through to medium, so the two boundaries naturally settle in medium.

---

### The solution

**Relative spending tiers**

```sql
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.2 THEN 'high'
        WHEN avg_spend < (SELECT AVG(total_amount) FROM transactions) * 0.8 THEN 'low'
        ELSE 'medium'
    END AS tier
FROM user_avg
```

> **Trick to solving**
>
> The tier threshold is not a constant, it is a query. The unlock is computing the population average once as a scalar and comparing every user's average to it; the bands are just that scalar times 1.2 and 0.8.

> **Common pitfall**
>
> Bucketing on raw `total_amount` per transaction instead of the per-user average. A single big-ticket order would then flag a user 'high' even if the rest of their orders are tiny. The tier is decided on the aggregated average, never a single row.

> **Interviewers watch for**
>
> They watch for whether you name the boundary as a computed scalar rather than a magic dollar number, and whether you compute that global average once instead of re-deriving it per row. Explaining why absolute cutoffs collapse on skewed spend data reads as senior.

> **Performance insight**
>
> With ~100M rows the per-user AVG scans and aggregates the fact table once; the scalar subquery is a second single aggregate the planner evaluates once, not per row. No self-join and no correlated re-scan, so the plan stays two cheap passes over the data.

---

## Common follow-up questions

- How would you turn the fixed plus/minus 20 percent bands into quartiles so the tiers stay balanced as spend drifts over time? _(Tests whether they can swap absolute 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 a CTE or cross join.)_
- What happens to a user whose only transactions are refunds, so their average is negative? _(Tests reasoning about sign and whether negative averages belong in 'low' or need special handling.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/spending_tiers)
- [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.