Employee Earnings SQL Challenge | Dataford Interview Questions

Your question is Employee Earnings SQL Challenge. Start with the requirements and the one table 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

IBM's watsonx workforce analytics team needs to identify the highest potential earnings among employees. Each employee has a monthly salary and a number of months worked.

Write a PostgreSQL query that calculates each employee's total earnings as monthly_salary * months_worked, finds the highest total earnings, and counts how many employees have exactly that amount.

Requirements

Use only records where monthly_salary and months_worked are not null and both values are nonnegative.
Return the highest total earnings as highest_total_earnings.
Return the number of employees with that exact amount as employee_count.
Use a CTE or equivalent intermediate result to avoid repeating the earnings calculation.

Schema

Employee

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
monthly_salary	NUMERIC(10,2)	Employee's monthly salary
months_worked	INTEGER	Number of months worked