How do you approach SQL: Customer Quartiles by Spend (NTILE)?

NTILE(4) splits the ordered partition into 4 nearly-equal-size buckets.

What are common mistakes on SQL: Customer Quartiles by Spend (NTILE)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Customer Quartiles by Spend (NTILE)

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. Given `Customers(id, total_spend)`, bucket customers into **quartiles by total_spend ascending** using `NTILE(4)`. Lower spend gets quartile 1; higher spend gets quartile 4. Tiebreak by id ascending. Return `[{ id, total_spend, quartile }]` sorted by quartile ascending, total_spend ascending, id ascending. **For the auto-grader:** implement `assignQuartiles(customers)`.

Example 1

Input: customers = [{id:1,total_spend:10},{id:2,total_spend:20},{id:3,total_spend:30},{id:4,total_spend:40}]

Output: [{id:1,total_spend:10,quartile:1},{id:2,total_spend:20,quartile:2},{id:3,total_spend:30,quartile:3},{id:4,total_spend:40,quartile:4}]

Constraints

- 1 <= customers.length <= 10^4 - total_spend >= 0

SQL: Customer Quartiles by Spend (NTILE): approach, complexity and pitfalls

A medium sql problem, graded against 3 test cases (1 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 2-step hint ladder

NTILE(4) splits the ordered partition into 4 nearly-equal-size buckets.
If the row count is not divisible by 4, the earlier buckets get the extra row each.

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT id, total_spend,

NTILE(4) OVER (ORDER BY total_spend ASC, id ASC) AS quartile

FROM Customers

ORDER BY quartile, total_spend, id;

```

NTILE bucketing rule: with `n` rows split into `k` buckets, the first `n mod k` buckets get `ceil(n/k)` rows; the remaining buckets get `floor(n/k)` rows. Example: 10 rows / 4 buckets -> sizes 3, 3, 2, 2.

When to prefer percentiles instead: `NTILE` is positional, not value-based. Two customers with identical spend can land in different buckets. If you need value-based bucketing, use `PERCENT_RANK` or `CUME_DIST` with explicit thresholds.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.