Top Percentile Spenders

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

L7

Asked in

Visa

Problem

The bar for the top third is not a dollar figure: it sits wherever buyers land against each other, each judged on everything they paid in transactions from 90 days before today onward rather than on any single purchase. Return the customers above that bar with each one's total, biggest spenders first. When two customers tie right at the bar, the lower user_id gets the spot.

Top 1% of users by total spend via percentile bucketing.

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

This is a relative cutoff dressed up as a VIP list. Nobody hands you a dollar figure; the bar sits wherever the top third of this window's buyers happens to land, so it moves every time the population does. Anyone can sum spend per customer. What separates candidates is splitting AFTER that sum: split the raw rows of transactions and you are sorting purchases, not people, and one lucky big order beats a customer who shopped all season. The quieter trap is the cut itself: two customers sit at exactly 2131.29 right on it.

Build it from the inside out

Step 1: Collapse the window to one row per customer

Filter transaction_date >= DATE('now', '-90 days') first, then SUM(total_amount) per user_id. The window counts back from today, not from the newest row in the table, and the earliest day is in. The date is stored as a date string, so the comparison is a plain inclusive bound. Filtering before the GROUP BY also means the engine only touches about 90 of the 365 partitions.

Step 2: Deal customers into three equal groups

Wrap that aggregate as spend_90d and apply NTILE(3) OVER (ORDER BY total_spend DESC, user_id). It deals customers into three piles as equal as the headcount allows, biggest first. The DESC is the whole question: flip it and band 1 becomes the smallest spenders. The user_id key earns its place too: two customers tie at the fifth and sixth slots, and without it the engine picks who makes the cut.

Step 3: Filter the band one level up, then order

A window result cannot be filtered in the same SELECT that computes it, so banded is its own derived table and the outermost query keeps spend_band = 1. Then sort by total_spend DESC so the list reads as a leaderboard, biggest spender on top.

SELECT user_id, total_spend
FROM (
  SELECT
    user_id,
    total_spend,
    NTILE(3) OVER (ORDER BY total_spend DESC, user_id) AS spend_band
  FROM (
    SELECT
      user_id,
      SUM(total_amount) AS total_spend
    FROM transactions
    WHERE transaction_date >= DATE('now', '-90 days')
    GROUP BY user_id
  ) AS spend_90d
) AS banded
WHERE spend_band = 1
ORDER BY total_spend DESC

Splitting raw rows

NTILE(3) OVER (ORDER BY total_amount DESC) straight on transactions sorts purchases. A customer can land in the top band twice, and a steady buyer with many mid-sized orders never makes it.

Splitting customer totals

NTILE(3) over spend_90d sorts people. Every customer appears once, judged on the whole window, which is the standing the question asks for.

Common follow-up questions

Common follow-up questions

• What if a tie at the cut should let both customers in? (Compare `RANK()` against a third of the headcount so equal totals land on the same side, accepting a band that is no longer exactly a third.)
• How would you return exactly a third of buyers, rounded down? (`ROW_NUMBER()` against `COUNT(*) / 3` gives a slice rounded down instead of leftovers going to the top.)
• How would you keep this cheap if it refreshed every hour? (Maintain a daily per-customer rollup so the 90-day total is a sum of about 90 small rows.)