Count weekly customers with YTD spend ≥ $1000

Q: Count weekly customers with YTD spend ≥ $1000

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.

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 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.

Count weekly customers with YTD spend ≥ $1000

Quick Overview

Comments (0)