Welcome to the SQL screen.
The question is on your right: Detect Recurring Expense Recording Issues. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |