Top Three Accounts by Monthly Transaction Amount
Mastercard● MediumFULLTIMEPHONE SCREENProblem statement
For each calendar month, find the accounts with the three highest distinct total transaction amounts.
Aggregate an account's transactions within the month, then apply dense ranking. Return every account whose monthly rank is at most three.
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 | — |
| account_id | Integer | No | — |
| transaction_date | Date | No | — |
| transaction_amount | Decimal | No | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| transaction_month | Text | No | — |
| account_id | Integer | No | — |
| total_transaction_amount | Decimal | No | — |
| account_rank | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- Format
transaction_monthasYYYY-MM. - Equal totals share one rank and may produce more than three rows.
- Sort months ascending, totals descending, and account IDs ascending.