Top Customers by Six-Month Transaction Amount
Mastercard● MediumFULLTIMEPHONE SCREENProblem statement
Find the ten customers with the highest total transaction amount during the six calendar months before the supplied as-of date.
The window starts six calendar months before as_of_date, inclusive, and ends at as_of_date, exclusive. Return customer_id and total_transaction_amount.
Table schema
MySQLPostgreSQLPandas
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
transactions
| Column | Type | Nullable | Description |
|---|---|---|---|
| transaction_idPK | Integer | No | — |
| customer_id | Integer | No | — |
| transaction_date | Date | No | — |
| transaction_amount | Decimal | No | — |
analysis_params
Contains exactly one row with param_id 1.
| Column | Type | Nullable | Description |
|---|---|---|---|
| param_idPK | Integer | No | — |
| as_of_date | Date | No | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| customer_id | Integer | No | — |
| total_transaction_amount | Decimal | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
analysis_paramscontains exactly one row withparam_id = 1.- Customers without an in-window transaction are omitted.
- Sort totals descending and break ties by customer ID ascending.