SQL Aggregation Over Positives and Negatives | Dataford Interview Questions

3Pillar's project delivery dashboard stores financial adjustments and credits as positive or negative work log values. Write a PostgreSQL query that reports the separate positive and negative totals for every project, including projects without work log entries.

Requirements

Use a CTE to classify each work log amount as either a positive value or a negative value.
Return one row per project with positive_sum and negative_sum.
Treat zero and NULL amounts as contributing zero to both totals.
Use a LEFT JOIN so projects without work logs remain in the result, and order the output by project ID.

Schema

projects

Column	Type	Description
project_idPK	INTEGER	Primary key for the project
project_name	VARCHAR(100)	Project name

work_logs

Column	Type	Description
work_log_idPK	INTEGER	Primary key for the work log
project_id	INTEGER	References projects.project_id and may be NULL for unassigned entries
amount	DECIMAL(12,2)	Positive, negative, zero, or NULL adjustment
entry_label	VARCHAR(100)	Short description of the work log entry

Your question is SQL Aggregation Over Positives and Negatives. 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