# Tiers of Want

> In each category, some carts weigh more than others. Sort the shoppers by what they spend.

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

A retail marketplace wants to see how its shoppers stack up inside each product category, scoring every shopper on their average basket size (total spend divided by number of purchases) in that category. Sort each shopper into a spend tier: High above $500, Medium from $200 up to $500, and Low below that. For each category and tier, report how many shoppers land there along with their combined transactions, combined sales, and the average basket size across them.

## Worked solution and explanation

### What this problem really is

Underneath the marketing language this is a two-stage rollup: build a per-user, per-category metric, bucket it, then roll those buckets up again. The trap is the grain. Label the tiers straight off the raw transaction rows, or sum total_sales after the second grouping without first collapsing to one row per user-category, and you silently multiply revenue by the number of users in each bucket. The join is the other landmine: get the products join wrong and you blend unrelated shoppers' spend into the wrong category before you ever compute a basket.

> **Trick to Solving**
>
> This is a two-stage aggregation problem. First aggregate to user-category grain, then classify and re-aggregate to category-segment grain.
> 
> 1. Join transactions to products for category
> 2. Compute basket size per user per category (SUM / COUNT)
> 3. Label each user-category pair as High/Medium/Low
> 4. Aggregate per category per segment

---

### Break down the requirements

#### Step 1: Compute basket size per user per category

Join `transactions` to `products`, GROUP BY `user_id, category`, compute `SUM(total_amount) * 1.0 / COUNT(*)` as basket_size.

#### Step 2: Label segments

Use CASE: `> 500` is 'High', 200 up to 500 inclusive is 'Medium', below 200 is 'Low'.

#### Step 3: Re-aggregate per category per segment

GROUP BY `category, segment`, then COUNT the user-category rows, SUM their transactions, SUM their sales, and AVG their basket sizes.

---

### The solution

**Two-stage aggregation with segment labeling**

```sql
WITH user_baskets AS (
    SELECT
        t.user_id,
        p.category,
        SUM(t.total_amount) AS total_sales,
        COUNT(*) AS txn_count,
        SUM(t.total_amount) * 1.0 / COUNT(*) AS basket_size,
        CASE
            WHEN SUM(t.total_amount) * 1.0 / COUNT(*) > 500 THEN 'High'
            WHEN SUM(t.total_amount) * 1.0 / COUNT(*) >= 200 THEN 'Medium'
            ELSE 'Low'
        END AS segment
    FROM transactions t
    JOIN products p ON t.product_id = p.product_id
    GROUP BY t.user_id, p.category
)
SELECT
    category,
    segment,
    COUNT(*) AS unique_users,
    SUM(txn_count) AS total_transactions,
    SUM(total_sales) AS total_sales,
    AVG(basket_size) AS avg_basket_size
FROM user_baskets
GROUP BY category, segment
```

> **Cost Analysis**
>
> The initial join and GROUP BY processes 120M transactions against 30K products, producing roughly 132M user-category combinations (6M users across 22 categories, with sparse coverage). The second aggregation collapses that down to about 66 rows (22 categories times 3 segments).

> **Interviewers Watch For**
>
> Boundary handling in the CASE expression. High is strictly above 500 and Medium runs from 200 up to and including 500, so a basket of exactly 500 is Medium, not High. Reversing the order of the WHEN clauses or using strict inequalities on both sides silently mislabels the edge cases.

> **Common Pitfall**
>
> Integer division when computing basket size. `SUM(total_amount) / COUNT(*)` truncates if the engine treats both as integers. Multiply by `1.0` to force decimal division before the values ever hit the CASE thresholds.

---

## Common follow-up questions

- What if a shopper has transactions in multiple categories? _(They appear in multiple user_baskets rows, one per category, and can sit in a different tier in each. This is correct per the requirements.)_
- How would you classify a shopper with a basket size of exactly $200 or $500? _(Clarify boundary conditions: the CASE uses `>= 200` for Medium and `> 500` for High, so both edges land in Medium.)_
- What if you needed to add a time dimension for monthly segmentation? _(Add date truncation to the first GROUP BY, turning it into a three-level aggregation over user, category, and month.)_
- How would you optimize this for a 10x larger dataset? _(Pre-compute the user_baskets CTE as a materialized summary table updated incrementally instead of rescanning 120M rows each run.)_

## Related

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