Your question is Window Functions for Duplicates. 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.
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.| 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 |