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.
Requirements
- Keep only the latest record for each
timesheet_id, based on updated_at.
- Parse the date and decimal hours, normalize billable flags, and retain approved rows with hours from 0 through 24.
- Use left joins to retain approved entries even when the consultant or project reference is missing. Label missing references as
Unknown consultant or Unknown project.
- Aggregate by month, consultant, and project, returning total, billable, non-billable hours, and entry count. Sort chronologically, then by consultant and project.
