Count weekly customers with YTD spend ≥ $1000
Company: Databricks
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: hard
Interview Round: Technical Screen
You have a transactions table:
transactions
- date (DATE) — transaction date in UTC
- transaction_id (STRING)
- customer_id (STRING)
- dollars (NUMERIC) — positive spend amount
Task (can be solved in SQL or in Python/pandas):
For each calendar week, compute the number of distinct customers whose **year-to-date (YTD)** cumulative spend is **at least $1000** as of the end of that week.
Definitions/assumptions:
- “Year-to-date” means from Jan 1 of the same calendar year through the end of the given week (inclusive).
- A “week” starts on Monday; report the week by its week_start_date.
- If the table contains multiple years, compute the metric independently per year.
Required output:
- year (INT)
- week_start_date (DATE)
- num_customers_ytd_ge_1000 (INT)
Order results by year, week_start_date.
Quick Answer: Evaluates cumulative aggregation and temporal grouping skills in the Data Manipulation (SQL/Python) domain, emphasizing window functions for year-to-date running totals, date/week bucketing, partitioning by year, deduplicating distinct customers, and considerations for aggregation performance.