Understanding Employee Deductions: An SQL Challenge from a FANNG Data Engineering Interview

The Problem Statement

In a recent Data Engineering interview at a leading product company, candidates were presented with an intriguing problem involving payroll data. The task was to identify employees whose annual deductions exceeded 50% of their total gross salary. Here’s a detailed breakdown of the problem and the solution:

Given Tables
Employee:

Empid
Name
Designation
Managerid
Level

Emppayroll:

Empid
PayrollFYyear
Payrolldate
Componentid
Amount

PayrollComponent:

Componentid
ComponentName
PayrollFYYear
IsDeduction (1 if the component is a deduction, 0 otherwise)

The SQL Query

To solve this problem, the following SQL query can be used:

SELECT Empid, PayrollFYyear
FROM Emppayroll e
LEFT JOIN PayrollComponent p ON e.componentid = p.Componentid
GROUP BY Empid, PayrollFYyear
HAVING SUM(CASE WHEN p.IsDeduction = 1 THEN e.Amount ELSE 0 END) > 
       SUM(e.Amount) / 2;

This solution identifies employees whose annual deductions exceed 50% of their total gross salary. It uses a LEFT JOIN to combine Emppayroll and PayrollComponent tables, linking them by Componentid. The query groups the results by Empid and PayrollFYyear, calculating total deductions and gross salary using conditional aggregation. The SUM function with CASE statements calculates total deductions (SUM(CASE WHEN p.IsDeduction = 1 THEN e.Amount ELSE 0 END)) and total gross salary (SUM(e.Amount )). The HAVING clause filters the groups where total deductions exceed half of the gross salary, ensuring only those employees are included in the final output.