Databricks·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Databricks data scientist interview with a SQL/Python take-home style problem. Pretty computational, no behavioral stuff from what I could tell. The question itself is well-designed but there are a few edge cases that'll bite you if you're not careful.

Questions Asked (1)

Given a transaction table with customer IDs, dates, and spend amounts, compute a weekly time series showing how many distinct customers have reached at least $1000 in year-to-date cumulative spend as of each week. Handle multi-year data by resetting the YTD counter on January 1st each year. Output year, week start date, and the customer count, ordered chronologically.

Product Analytics & MetricsData Modeling

Author's notes

The core join/window logic isn't too bad once you see it, but I fumbled the multi-year reset part initially.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by computing daily cumulative spend per customer within each year, then determine the first date each customer crosses the $1000 threshold. For each week, count distinct customers whose threshold date is on or before the week start date, ensuring the count resets annually. Finally, generate a complete weekly time series and join the counts.

Pro tip: Clarify the definition of 'week start' (e.g., Monday) and whether the count should include customers who reached the threshold in prior weeks. Also, consider using a calendar table to handle weeks with no new qualifiers, ensuring a continuous time series.

1. Define the weekly calendar

Create a table of all week start dates covering the entire date range in the data, ensuring no gaps. This will be the backbone for the time series.

2. Compute YTD cumulative spend per customer

For each customer and each transaction date, calculate the running total of spend within the year, resetting on January 1st. Use window functions partitioned by customer and year, ordered by date.

3. Identify first threshold crossing date

For each customer and year, find the earliest date where the YTD cumulative spend reaches or exceeds $1000. This is the date they become 'qualified'.

4. Join to weekly calendar and count distinct customers

For each week start date, count distinct customers who have a threshold crossing date on or before that week start date and within the same year. Ensure the count resets annually by grouping by year.

5. Output and order results

Select year, week start date, and customer count, ordered chronologically by year and week start date. Handle weeks with zero new qualifiers by including them with the cumulative count.

Key Points to Mention

Use of window functions to compute running totals (e.g., SUM() OVER (PARTITION BY customer_id, year ORDER BY date))
Resetting the cumulative sum at the start of each year (partitioning by year)
Identifying the first date a customer crosses the $1000 threshold (e.g., using MIN() with a condition)
Generating a complete weekly time series to avoid missing weeks (using a calendar table or recursive CTE)
Counting distinct customers cumulatively up to each week (using a self-join or window function)
Handling edge cases such as multiple transactions on the same day, customers with no qualifying spend, and weeks with no new qualifiers

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.