Window Functions for Duplicates | Dataford Interview Questions

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.

Requirements

Join each worklog to its consultant and exclude worklogs whose consultant does not exist.
Identify duplicate business records using consultant_id, project_code, work_date, hours_logged, and activity_code. Return a duplicate count, a duplicate row number, and an is_duplicate flag.
Within each project, calculate 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().
Return only worklogs dated from 2025-01-01 through 2025-01-31, ordered by project, date, and log ID.

Schema

consultants

Column	Type	Description
consultant_idPK	INT	Unique consultant identifier
consultant_name	VARCHAR(100)	Consultant full name
specialty	VARCHAR(80)	Primary delivery specialty

work_logs

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

Your question is Window Functions for Duplicates. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem