Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
Window Functions for Duplicates
00:00
5 left

Window Functions for Duplicates

MediumSQL · PostgreSQL

Problem

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

  1. Join each worklog to its consultant and exclude worklogs whose consultant does not exist.
  2. 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.
  3. 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().
  4. Return only worklogs dated from 2025-01-01 through 2025-01-31, ordered by project, date, and log ID.

Output details

  • Return columns in this order: 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.
  • Sort final rows by project_code, work_date, then log_id, all ascending.
  • Assign duplicate_row_number by log_id ascending within each duplicate business-record group.
  • Order each project's running totals, LAG, and LEAD by work_date ascending, then log_id ascending.
  • Rank NULL hours_logged values last, and let running totals ignore NULL hours while preserving NULL values for LAG and LEAD.

Schema

consultants
ColumnTypeDescription
consultant_idPKINTUnique consultant identifier
consultant_nameVARCHAR(100)Consultant full name
specialtyVARCHAR(80)Primary delivery specialty
work_logs
ColumnTypeDescription
log_idPKINTUnique worklog identifier
consultant_idINTReferences consultants.consultant_id
project_codeVARCHAR(20)Project identifier
work_dateDATEDate when work was performed
hours_loggedNUMERIC(6,2)Hours recorded for the worklog
activity_codeVARCHAR(20)Activity classification
Tablesconsultantswork_logs
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the two tables are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.