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

Loading editor...