Tiers of Want

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

HealthTap

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.

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

Tables

transactions

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

products

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

Expected output

Worked solution and explanation

What this problem really is

This is a two-level rollup dressed up as customer segmentation. The tier belongs to a shopper inside a category, and that shopper's average basket only exists after you collapse transactions to one row per user_id and category. Anyone can write the CASE. What separates candidates is where it runs. Put it on raw total_amount and you tier purchases instead of people, and COUNT(*) then reports transactions where the question wants shoppers. The quieter trap is the last column. SUM(total_sales) / SUM(txn_count) at the tier grain looks like an average basket, but it weights every shopper by how often they buy. On this seed Automotive's High tier is 665.53 as a mean of the shoppers' baskets and 698.54 as a pooled ratio.

Break down the requirements

Step 1: Compute basket size per shopper per category

Join transactions to products for the category, group by user_id, category, and compute SUM(total_amount) * 1.0 / COUNT(*) as basket_size. Every row is one purchase, so COUNT(*) is the right denominator, not SUM(quantity).

Step 2: Label segments on the shopper row

Apply the CASE to that per-shopper average: > 500 is 'High', >= 200 is 'Medium', everything else 'Low'. Because the WHEN clauses are tested in order, the High check must come first.

Step 3: Re-aggregate per category per segment

Group the shopper rows by category, segment. COUNT(*) now counts shoppers, SUM(txn_count) and SUM(total_sales) add up their activity, and AVG(basket_size) gives each shopper one equal vote.

The solution

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

Pooled ratio

SUM(total_sales) / SUM(txn_count) at the tier grain. A shopper with eight purchases pulls the figure twice as hard as one with four, so frequent buyers decide the number. Automotive High reads 698.54.

Mean of shopper baskets

AVG(basket_size) over the user_baskets rows. Each shopper contributes their own average once, which is what 'the average basket size across them' asks for. Automotive High reads 665.53.

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? (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 a month bucket of `transaction_date` to the first grouping, so the tier is assigned per user, category and month.)
• How would you optimize this for a 10x larger dataset? (Materialize `user_baskets` as a summary table updated incrementally instead of rescanning the full transaction log each run.)