SQL Join with Aggregation | Dataford Interview Questions

Your question is SQL Join with Aggregation. 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.

Problem

The Alten Delivery Centre Spain needs a quarterly view of approved, billable work logged against active delivery projects. Write a PostgreSQL query that joins project and work-log data and returns only projects meeting the minimum effort threshold.

Requirements

Consider active projects in the Alten Delivery Centre Spain portfolio.
Include only approved, billable work logs recorded from 2025-01-01 through 2025-03-31.
Return each qualifying project's ID, name, delivery centre, manager, total approved billable hours, and log count.
Keep projects with no qualifying logs during the join, but return only projects with at least 20 approved billable hours. Classify totals of 40 hours or more as High, otherwise as Standard.

Schema

projects

Column	Type	Description
project_idPK	INTEGER	Unique project identifier
project_name	VARCHAR(100)	Project name
delivery_centre	VARCHAR(80)	Alten delivery centre responsible for the project
manager_name	VARCHAR(100)	Assigned project manager
project_status	VARCHAR(20)	Current project lifecycle status

work_logs

Column	Type	Description
log_idPK	INTEGER	Unique work-log identifier
project_id	INTEGER	Project associated with the work log
logged_on	DATE	Date on which work was recorded
hours	NUMERIC(8,2)	Recorded work hours
log_status	VARCHAR(20)	Approval status of the work log
is_billable	BOOLEAN	Indicates whether the work is billable