Find top buyer in earliest 10-year window
Company: Wayfair
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Take-home Project
You are given two tables: `customers` and `purchases`.
### Table: `customers`
| column | type | notes |
|---|---|---|
| customer_id | BIGINT | Primary key |
| customer_name | VARCHAR | |
| purchase_id | VARCHAR | Foreign key to `purchases.purchase_id` (assume 1 row per purchase) |
### Table: `purchases`
| column | type | notes |
|---|---|---|
| purchase_id | VARCHAR | Primary key |
| purchase_price | DECIMAL(10,2) | Purchase amount |
| purchase_date | DATE | Date of purchase |
### Task
Define the **earliest 10-year window** as the interval starting at the **minimum** `purchase_date` in `purchases` and ending 10 years later.
Within that earliest 10-year window, find the customer(s) who have the **highest purchase price**.
Return:
- `customer_name`
- `highest_price` (that customer’s max price within the window)
If multiple customers tie for the highest price, return **all** of them.
### Output
A result set with columns: `(customer_name, highest_price)`.
### Assumptions
- Use the same timezone/calendar as stored dates (DATE, no timezone handling required).
- The 10-year window is `[min_date, min_date + INTERVAL '10 years')` unless your SQL dialect requires a different but equivalent expression.
Quick Answer: This question evaluates proficiency in data manipulation and analytical querying with SQL/Python, emphasizing date-based windowing, join operations, aggregation to identify maxima, and handling tied results.