Diverse Shoppers

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
L3

Problem

The retention team is building a loyalty tier for shoppers who range widely across the catalog rather than repeat-buying one item. For each customer who has purchased at least six different products, show their username, how many different products they have bought, and their total spend across all transactions.

They shop the whole catalog.

Tables

users

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

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

transactions

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

transactions: 10 of 200 rows
transaction_iduser_idproduct_idquantitytotal_amounttransaction_date
10671971001223.462026-02-02
11342941050336.932026-03-03
12013911099450.42026-04-04
12684881148563.872026-05-05
13355851197002026-06-06
14026821246290.812026-07-07
146977912953104.282026-08-08
153687613444117.752026-09-09
160397313935-452026-01-10
1670107014421144.692026-02-11

Expected output

First 5 of 12 rows
usernamedistinct_productstotal_spend
alice68605.98
aaron4278814.54
amelia69003.12
arjun 69191.7
ava9979380.28

Worked solution and explanation

What this problem really is

Strip the loyalty-tier framing and this is a product-variety count with a cutoff you can only apply after aggregating. The real question: can you count DISTINCT products per buyer, not transactions, and then filter the groups on that count? A plain WHERE cannot touch a per-group aggregate, and COUNT(*) counts repeat purchases, so the shopper who bought one hoodie eight times looks identical to one who tried eight different items. Get either wrong and your loyalty tier fills with single-item repeat buyers, the exact customers you meant to exclude.

Break down the requirements

Step 1: Join users to transactions

Join on user_id so each transaction carries its buyer's username. An inner join is what you want here: it drops users who never bought anything, which is correct because the tier is about buyers.

Step 2: Aggregate per user

COUNT(DISTINCT product_id) gives product variety (re-buying the same item does not inflate it), and SUM(total_amount) gives total spend. Both are computed per user with GROUP BY u.user_id, u.username.

Step 3: Filter and sort

HAVING COUNT(DISTINCT product_id) >= 6 keeps only the broad shoppers. This has to be HAVING, not WHERE: the count only exists after the rows are grouped. Then ORDER BY total_spend DESC, u.username for a stable highest-first order.

The solution

Multi-metric aggregation with a variety cutoff
SELECT
    u.username,
    COUNT(DISTINCT t.product_id) AS distinct_products,
    SUM(t.total_amount) AS total_spend
FROM users u
JOIN transactions t ON u.user_id = t.user_id
GROUP BY u.user_id, u.username
HAVING COUNT(DISTINCT t.product_id) >= 6

Common follow-up questions