Welcome to the SQL screen.
The question is on your right: Clean Inconsistent Expense Entries. Read through the requirements and the two 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?
Write a PostgreSQL query that cleans inconsistent department, category, and amount fields from CU Denver Finance expense staging data, then returns department-level totals for valid expenses only. Exclude unmatched departments and invalid or negative amounts, and keep only departments with at least 2 valid cleaned rows.
| Column | Type | Description |
|---|---|---|
| expense_id | INT | Raw expense row ID |
| dept_name_raw | VARCHAR(100) | Department name from source extract |
| category_raw | VARCHAR(50) | Raw category text |
| amount_raw | VARCHAR(30) | Amount stored as text |
| expense_date | DATE | Expense date |
| Column | Type | Description |
|---|---|---|
| dept_code | VARCHAR(10) | Department code |
| dept_name_standard | VARCHAR(100) | Standard department name |
| FIN | Finance |
| BUD | Budget Office |
| SA | Student Affairs |
| 1 | finance | travel | $1,200.50 | 2024-01-10 |
| 4 | Budget Office | supplies | -50 | 2024-01-18 |
| 8 | Student Affairs | travel | abc | 2024-02-01 |
| 10 | Unknown Dept | Supplies | $100 | 2024-02-05 |