Inactive Customers with Prior Activity
Mastercard● MediumFULLTIMEPHONE SCREENProblem statement
Find customers who made at least one transaction before the one-year lookback window but made no transaction during that window.
The window begins one calendar year before as_of_date, inclusive, and ends at as_of_date, exclusive. Customers who never transacted do not qualify.
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.
customers
| Column | Type | Nullable | Description |
|---|---|---|---|
| customer_idPK | Integer | No | — |
| customer_name | Text | No | — |
transactions
| Column | Type | Nullable | Description |
|---|---|---|---|
| transaction_idPK | Integer | No | — |
| customer_id | Integer | No | — |
| transaction_date | Date | No | — |
| transaction_amount | Decimal | No | — |
Foreign key: customer_id → customers(customer_id)
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 | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
analysis_paramscontains exactly one row withparam_id = 1.- Future-dated transactions do not count as prior or recent activity.
- Return customer IDs ascending.