Capgemini delivery teams use worklogs to monitor project effort and billing quality. Write a PostgreSQL query using the consultants and work_logs tables to audit January 2025 worklogs.
consultant_id, project_code, work_date, hours_logged, and activity_code. Return a duplicate count, a duplicate row number, and an is_duplicate flag.DENSE_RANK() by hours_logged descending, a running total of hours by work date and log_id, and the previous and next logged hours using LAG() and LEAD().2025-01-01 through 2025-01-31, ordered by project, date, and log ID.project_code, log_id, consultant_name, work_date, hours_logged, duplicate_count, duplicate_row_number, is_duplicate, hours_rank, running_hours, previous_hours, next_hours.project_code, work_date, then log_id, all ascending.duplicate_row_number by log_id ascending within each duplicate business-record group.LAG, and LEAD by work_date ascending, then log_id ascending.hours_logged values last, and let running totals ignore NULL hours while preserving NULL values for LAG and LEAD.| Column | Type | Description |
|---|---|---|
| consultant_idPK | INT | Unique consultant identifier |
| consultant_name | VARCHAR(100) | Consultant full name |
| specialty | VARCHAR(80) | Primary delivery specialty |
| Column | Type | Description |
|---|---|---|
| log_idPK | INT | Unique worklog identifier |
| consultant_id | INT | References consultants.consultant_id |
| project_code | VARCHAR(20) | Project identifier |
| work_date | DATE | Date when work was performed |
| hours_logged | NUMERIC(6,2) | Hours recorded for the worklog |
| activity_code | VARCHAR(20) | Activity classification |