Find Monthly Top Customers Excluding High-Frequency Users
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
# Find Monthly Top Customers Excluding High-Frequency Users
Write one PostgreSQL SELECT statement or CTE query. Do not create, alter, or modify tables.
## Schema
delivery_orders
| column | type | description |
|---|---|---|
| delivery_id | integer | Unique delivery |
| order_place_time | timestamp | Order placement time |
| customer_id | integer | Customer |
| restaurant_id | integer | Restaurant |
| dasher_id | integer | Courier |
## Task
For each calendar month, exclude customers with more than 30 deliveries in that month. Among the remaining customers, return every customer tied for the highest delivery count.
## Required Output
Return order_month, customer_id, and delivery_count. Sort by month ascending and customer ID ascending.
## Constraints
- High-frequency status is evaluated independently each month.
- Return all ties.
- Months with no remaining customer produce no row.
```hint Rank after filtering
Count each customer-month, remove counts above 30, then use a tie-preserving rank within month.
```
Quick Answer: A PostgreSQL interview problem about ranking monthly customers while excluding users who meet a high-frequency threshold. Candidates must build the eligible population correctly, aggregate orders, apply deterministic ranking, and handle ties explicitly.