Count weekly customers with ≥$1000 YTD spend

Question

You are given a transaction-level table and must compute a weekly time series of how many customers have reached a year-to-date spending threshold. The problem can be solved in SQL or Python (pandas).

Table

transactions

• date (DATE) — transaction date (assume UTC, calendar dates)
• transaction_id (STRING/INT) — primary key
• customer_id (STRING/INT)
• dollars (NUMERIC) — non-negative spend amount in USD

Definitions

• Week: a calendar week starting on Monday. Report each week by its week_start_date (the Monday of the week). You may pick a different, consistent week boundary if you state it.
• Year-to-date (YTD) spend as of a week: for a given customer_id in calendar year Y, the sum of dollars from Jan 1 of year Y up to and including the last day of that week.

Task

For each week present in the data, compute the number of distinct customers whose YTD spend as of that week is at least $1000.

1. Core metric. For every week, count the distinct customers whose cumulative spend from the start of that calendar year through the end of the week is ≥ $1000.
2. Multi-year handling. If the data spans multiple calendar years, compute the metric independently per year — YTD resets to $0 on Jan 1 of each year. Include the year in the output.
3. Ordering. Order the results by year, then week_start_date.

Output

Return a table with:

• year (INT)
• week_start_date (DATE)
• num_customers_ge_1000_ytd (INT)

(If you assume a single year, the original minimal output of week_start and num_customers_ge_1000_ytd is also acceptable — the year column is what generalizes it to multiple years.)

Count weekly customers with ≥ $1000 year-to-date spend

You are given a transactions table with columns (txn_date, transaction_id, customer_id, dollars).

For each calendar week (weeks start on Monday) between the earliest and latest transaction in the table, compute the number of distinct customers whose cumulative year-to-date (YTD) spend for 2025 is at least $1000 as of the end of that week.

Notes:

• YTD for 2025 means summing dollars from 2025-01-01 up through that week.
• A customer should continue to be counted in later weeks after they first reach $1000 YTD, even if they have no transactions in those later weeks.
• Return one row per week with: week_start_date, customers_with_1000_ytd.

Assume a PostgreSQL-like SQL dialect (supports date_trunc, window functions, and recursive CTEs).

Table transactions

column	type	constraints
transaction_id	INT	PRIMARY KEY
txn_date	DATE	NOT NULL
customer_id	INT	NOT NULL
dollars	DECIMAL(10,2)	NOT NULL

Sample data:

customer_id	dollars	transaction_id	txn_date
101	400	1	2025-01-03
101	300	2	2025-01-10
101	200	3	2025-01-17
101	150	4	2025-01-24
102	600	5	2025-02-01
102	500	6	2025-02-08
103	1000	7	2025-03-05
104	200	8	2025-01-12
104	300	9	2025-02-20
104	400	10	2025-03-10

Expected output

One row per Monday week start date from the earliest to latest transaction week, with the count of customers whose 2025 YTD cumulative spend is at least 1000 by the end of that week.

customers_with_1000_ytd	week_start_date
0	2024-12-30
0	2025-01-06
0	2025-01-13
1	2025-01-20
1	2025-01-27
2	2025-02-03
2	2025-02-10
2	2025-02-17
2	2025-02-24
3	2025-03-03
3	2025-03-10

Hints

1. First aggregate dollars to weekly spend per customer, then compute a running (YTD) sum with a window function.
2. To keep counting customers in weeks with no transactions, generate all weeks in the range and left join weekly spend, filling missing weeks with 0.

Solution

This screen tests cumulative (running-total) aggregation, per-year partitioning that resets YTD, date/week bucketing to Monday, and a distinct count over a sticky threshold. The key insight separating a correct answer from a plausible-but-wrong one: spend must be accumulated per customer and the $1000 threshold applied per customer, after which you count distinct customers per week — and a customer who crosses the threshold remains counted for every later week of that year, including weeks with no transactions. A strong candidate handles the multi-year reset, picks a consistent week boundary, and reasons about why summing per-week distinct counts or thresholding an aggregate running total is incorrect.

The core trap is that a customer who crosses $1000 stays counted for the rest of the year — so you cannot simply tally per-week new-spenders, and you must accumulate spend per customer before thresholding, not aggregate first and threshold a running total of the aggregate.

Approach (two stages):

1. Per-customer, per-week cumulative YTD. Bucket each transaction into its (year, week_start) and sum dollars per (customer_id, year, week). Then take a running cumulative sum over weeks within each (customer_id, year), partitioned by year so YTD resets every Jan 1.
2. Threshold then count distinct. For each (year, week), count how many customers have cumulative YTD ≥ 1000 as of that week. Because the cumulative sum is monotonically non-decreasing within a year, once a customer reaches the threshold they remain counted in every later week of that year.

A subtlety: a customer only has rows for weeks in which they transacted. To count them in intervening weeks where they didn't spend (but their YTD is still ≥ 1000), you must carry their cumulative value forward across all weeks of the year. The cleanest way is to evaluate, for each week present in the data, every customer whose first week of reaching ≥ $1000 is ≤ that week.

SQL (window functions):

WITH per_week AS (             -- spend per customer per week
  SELECT
    customer_id,
    EXTRACT(YEAR FROM date)::int                         AS yr,
    DATE_TRUNC('week', date)::date                       AS week_start,  -- Monday
    SUM(dollars)                                         AS week_spend
  FROM transactions
  GROUP BY 1, 2, 3
),
cum AS (                      -- running YTD per customer within each year
  SELECT
    customer_id, yr, week_start,
    SUM(week_spend) OVER (
      PARTITION BY customer_id, yr
      ORDER BY week_start
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ytd_spend
  FROM per_week
),
threshold_week AS (           -- first week each customer hits >= 1000, per year
  SELECT customer_id, yr, MIN(week_start) AS hit_week
  FROM cum
  WHERE ytd_spend >= 1000
  GROUP BY customer_id, yr
),
weeks AS (                    -- every (year, week) present in the data
  SELECT DISTINCT yr, week_start FROM per_week
)
SELECT
  w.yr                                   AS year,
  w.week_start                           AS week_start_date,
  COUNT(DISTINCT t.customer_id)          AS num_customers_ge_1000_ytd
FROM weeks w
LEFT JOIN threshold_week t
  ON t.yr = w.yr
 AND t.hit_week <= w.week_start          -- counted from the week they crossed onward
GROUP BY w.yr, w.week_start
ORDER BY w.yr, w.week_start;

The threshold_week CTE collapses each customer to the first week they reach $1000 in a given year; joining hit_week <= week_start then counts them in that week and every later week of the same year automatically — no forward-fill of empty weeks needed. DATE_TRUNC('week', ...) returns the Monday in Postgres/Spark SQL (Databricks); adjust the bucketing function for other engines.

Python (pandas):

import pandas as pd

def weekly_ge_1000(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    df['date'] = pd.to_datetime(df['date'])
    df['year'] = df['date'].dt.isocalendar().year      # ISO year aligns with Monday weeks
    # Monday of the week containing each date:
    df['week_start'] = (df['date'] - pd.to_timedelta(df['date'].dt.weekday, unit='D')).dt.normalize()

    # 1) per customer, per (year, week) spend
    wk = (df.groupby(['customer_id', 'year', 'week_start'], as_index=False)['dollars']
            .sum()
            .sort_values(['customer_id', 'year', 'week_start']))

    # 2) running YTD per customer within each year
    wk['ytd'] = wk.groupby(['customer_id', 'year'])['dollars'].cumsum()

    # 3) first week each customer crosses 1000 (per year)
    hit = (wk[wk['ytd'] >= 1000]
             .groupby(['customer_id', 'year'], as_index=False)['week_start'].min()
             .rename(columns={'week_start': 'hit_week'}))

    # 4) for every (year, week) in the data, count customers whose hit_week <= week
    weeks = wk[['year', 'week_start']].drop_duplicates()
    merged = weeks.merge(hit, on='year', how='left')
    counted = merged[merged['hit_week'] <= merged['week_start']]
    out = (counted.groupby(['year', 'week_start'])['customer_id']
                  .nunique()
                  .reset_index(name='num_customers_ge_1000_ytd'))

    # weeks where nobody has crossed yet should still appear with 0
    out = (weeks.merge(out, on=['year', 'week_start'], how='left')
                .fillna({'num_customers_ge_1000_ytd': 0}))
    out['num_customers_ge_1000_ytd'] = out['num_customers_ge_1000_ytd'].astype(int)
    return (out.rename(columns={'week_start': 'week_start_date'})
               .sort_values(['year', 'week_start_date'])
               .reset_index(drop=True))

Points the interviewer probes:

• Accumulate per customer, then threshold — not the reverse. Thresholding a running total of aggregate spend, or summing per-week distinct counts, both give wrong answers. The threshold is per customer, the distinct count is per week.
• Stickiness. Once a customer is ≥ $1000 in a year, they stay counted for all later weeks that year even with no further spend. The hit_week <= week_start join (or a forward-fill of the cumulative value) captures this; a naive per-week filter on rows that exist would undercount the quiet weeks.
• YTD resets per year. Partition the running sum by (customer_id, year) so Jan 1 zeroes it out; don't let December carry into January.
• Consistent week boundary. Pick Monday (or any boundary) and bucket every date the same way; mismatched bucketing between the cumulative and the reporting step silently corrupts counts. Using ISO year keeps the Monday-week and the year consistent at year boundaries.
• Distinct customers. Use COUNT(DISTINCT customer_id) / nunique; a customer with many transactions in a week must count once.
• Complexity. O(N log N) dominated by sorting/grouping the transactions; the window cumulative sum and the final group-by are linear in the number of (customer, week) rows.