Your question is Clean and Reshape Timesheet Data. 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.
Tessella's analytics team receives timesheet extracts with inconsistent formatting, duplicate updates, missing references, and mixed approval states. Write a PostgreSQL query that converts the raw extract into a monthly analysis-ready table.
timesheet_id, based on updated_at.Unknown consultant or Unknown project.| Column | Type | Description |
|---|---|---|
| timesheet_id | INT | Logical identifier for a timesheet entry |
| ingestion_idPK | INT | Unique raw ingestion row identifier |
| consultant_id | INT | Consultant reference |
| project_code | VARCHAR(20) | Project reference |
| work_date_text | VARCHAR(20) | Work date stored as text in DD/MM/YYYY format |
| hours_text | VARCHAR(20) | Hours stored as text |
| billable_text | VARCHAR(10) | Text representation of billable status |
| status | VARCHAR(20) | Timesheet approval status |
| updated_at | TIMESTAMP | Timestamp of the raw row update |
| Column | Type | Description |
|---|---|---|
| consultant_idPK | INT | Consultant identifier |
| consultant_name | VARCHAR(100) | Consultant full name |
| Column | Type | Description |
|---|---|---|
| project_codePK | VARCHAR(20) | Project code |
| project_name | VARCHAR(100) | Project name |