Top Earners via Table Join | Dataford Interview Questions

Your question is Top Earners via Table Join. 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

Motorola Solutions is reviewing compensation data for employees supporting the APX radio product line. Write a PostgreSQL query to find the top three employees by total recorded earnings.

Requirements

Use a LEFT JOIN from employees to employee_earnings so employees without earnings records remain eligible with total earnings of zero.
Aggregate all earning records for each employee and treat NULL earning amounts as zero.
Rank employees by total earnings descending, breaking ties with the lower employee_id.
Return exactly the top three employees, including their rank, employee ID, name, and total earnings.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
job_title	VARCHAR(100)	Employee's role

employee_earnings

Column	Type	Description
earning_idPK	INT	Unique earning record identifier
employee_id	INT	Employee receiving the earning
earning_amount	DECIMAL(12,2)	Recorded earning amount
earning_type	VARCHAR(30)	Type of earning record