Find top buyer in earliest 10-year window
Quick Overview
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.
Wayfair
Feb 18, 2026, 1:21 AM
Data Scientist
Take-home Project
Data Manipulation (SQL/Python)
0
0
Loading...
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.