SQL Productivity by Shift | Dataford Interview Questions

Write a SQL query to calculate labor hours, package volume, and productivity by shift using multiple tables.

Use the provided UPS shift, labor, and package-volume data. Include every shift, treat missing labor or package values as zero, and return productivity as package volume divided by labor hours. Return NULL productivity when labor hours are zero.

Output

One row per shift with shift_date, shift_name, labor_hours, package_volume, and productivity
Order by shift_date, shift_name, and shift_id

Schema

shifts

Column	Type	Description
shift_idPK	INT	Unique shift identifier
shift_date	DATE	Operating date of the shift
shift_name	VARCHAR(30)	Shift label such as Day or Night

labor_hours

Column	Type	Description
labor_idPK	INT	Unique labor record identifier
shift_id	INT	Referenced shift
employee_id	INT	Employee assigned to the shift
hours_worked	NUMERIC(6,2)	Hours worked by the employee

package_volume

Column	Type	Description
volume_idPK	INT	Unique package-volume record identifier
shift_id	INT	Referenced shift
package_count	INT	Packages processed in the record

Your question is SQL Productivity by Shift. 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