Your question is Clean Inconsistent Expense Entries. Start with the requirements and the two 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.
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 |