Your question is Detect Recurring Expense Recording Issues. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given expense transactions, category metadata, and recording rules. Your task is to identify expense categories that show a recurring recording issue in the last 90 days: categories with at least 3 invalid expenses and an invalid rate above 25%. Return the category name, total expense count, invalid expense count, and invalid rate, sorted by the highest invalid rate.
| Column | Type | Description |
|---|---|---|
| category_idPK | INT | Primary key for the expense category |
| category_name | VARCHAR(100) | Human-readable category name |
| active_flag | BOOLEAN | Whether the category is active |
| Column | Type | Description |
|---|---|---|
| expense_idPK | INT | Primary key for the expense record |
| category_id | INT | Foreign key to expense_categories.category_id |
| expense_date | DATE | Date the expense was recorded |
| amount | NUMERIC(12,2) | Expense amount |
| status | VARCHAR(20) | Recorded status such as approved, pending, or invalid |
| Column | Type | Description |
|---|---|---|
| statusPK | VARCHAR(20) | Status value allowed by the recording policy |
| is_allowed | BOOLEAN | Whether the status is allowed |