25. Customer Spend Quartiles (NTILE)
Difficulty: Medium · Topics: Window Functions
Marketing wants to split customers into four equal-sized groups by total_spend, highest spenders in quartile 1.
Return customer_id, total_spend and quartile. Break ties in spend by the smaller customer_id first. When the count does not divide evenly, the first quartiles get one extra customer.
Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
customers
| customer_id | total_spend |
|---|---|
| 1 | 500 |
| 2 | 300 |
| 3 | 900 |
| 4 | 100 |
| 5 | 700 |
| 6 | 200 |
| 7 | 800 |
| 8 | 400 |
Expected output
| customer_id | total_spend | quartile |
|---|---|---|
| 1 | 500 | 2 |
| 2 | 300 | 3 |
| 3 | 900 | 1 |
| 4 | 100 | 4 |
| 5 | 700 | 2 |
| 6 | 200 | 4 |
| 7 | 800 | 1 |
| 8 | 400 | 3 |
Hints
Hint 1
F.ntile(4) over a window ordered by spend descending.Hint 2
Addcustomer_id as a second ordering key so ties are deterministic.Hint 3
ntile hands out remainder rows to the lowest bucket numbers first.PySpark functions you'll practise
- Ntile
- Window spec
- orderBy
Related problems
- Latest Order per Customer · Medium · Window Functions
- Running Total by Region · Medium · Window Functions
- Top Two Salary Levels per Department · Medium · Window Functions
- Month-over-Month Change · Medium · Window Functions
- Three-Day Login Streak · Hard · Window Functions
Browse
Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings
Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems