Count weekly customers with ≥$1000 YTD spend

Q: Count weekly customers with ≥$1000 YTD spend

This question evaluates proficiency in time-series aggregation, cumulative year-to-date computations, distinct-count deduplication, and use of SQL or pandas for data manipulation and reporting.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

You are given a transaction-level table and need to compute a weekly time series.

Table

transactions

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

Definitions

Week : calendar week starting on Monday (you may state a different week boundary, but be consistent).
Year-to-date (YTD) spend as of a week : for a given customer_id and a given week w within year Y , the sum of dollars from Jan 1 of year Y up to and including the last day of week w .

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.

Output

Return a table with:

week_start (DATE)
num_customers_ge_1000_ytd (INT)

You may solve this in SQL or Python (pandas).

Count weekly customers with ≥$1000 YTD spend

Overview

Table

Definitions

Task

Output

Comments (0)