SQL Aggregates Over Salaries | Dataford Interview Questions

Your question is SQL Aggregates Over Salaries. Start with the requirements and the three 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

Swiggy's Business Analytics team needs a payroll snapshot for currently active employees. Write a PostgreSQL query that combines employee, department, and salary data to calculate salary statistics.

Requirements

Include only employees whose employment_status is Active.
Join employees to departments so that only employees assigned to a valid department are included.
Preserve active employees even when their salary is missing, allowing PostgreSQL's aggregate functions to handle NULL values correctly.
Return one row containing the total salary, minimum salary, and maximum salary.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	Assigned department identifier
employment_status	VARCHAR(20)	Current employment status

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Swiggy department name

employee_salaries

Column	Type	Description
employee_idPK	INT	Employee identifier
monthly_salary	NUMERIC(12,2)	Monthly salary in INR