Spark and SQL Fundamentals | Dataford Interview Questions

Some technical related to spark and sql

Assume the provided tables contain departments, employees, and assignments. Write a PostgreSQL-compatible query that summarizes completed assignments during January 2025. Include every department, including departments with no qualifying assignments.

Output

One row per department with department_name, completed_assignments, participating_employees, and average_completed_hours
Use zero for count or average values when no qualifying assignments exist
Order by department_name ascending, then department_id ascending

Schema

departments

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

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee name
department_id	INT	Employee department identifier

assignments

Column	Type	Description
assignment_idPK	INT	Unique assignment identifier
employee_id	INT	Assigned employee identifier
assignment_date	DATE	Assignment date
status	VARCHAR(20)	Assignment status
hours	DECIMAL(8,2)	Hours recorded for the assignment

Your question is Spark and SQL Fundamentals. Start with the requirements and the three 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