Deep SQL and Recursion Interview

HardSQL · PostgreSQL

Problem

Describe a time you were tested on deep SQL topics like complex joins and also on Python recursion and cloud computing concepts.

For the SQL portion, write a PostgreSQL query against the supplied tables to identify employees whose billable hours increased in a month and who worked on at least two distinct projects during that month.

Output

One row per qualifying employee-month.
Return employee_name, manager_name, department_name, month_start, billable_hours, prior_month_hours, change_percent, project_count, and department_rank.
Include only qualifying increases with a positive prior-month value, and order by department_name, month_start, and employee_name.

Schema

departments

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

employees

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

projects

Column	Type	Description
project_idPK	INT	Project identifier
project_name	VARCHAR(100)	Project name
department_id	INT	Owning department

work_logs

Column	Type	Description
log_idPK	INT	Work log identifier
employee_id	INT	Employee who recorded the work
project_id	INT	Project associated with the work
work_date	DATE	Date of the work
hours	DECIMAL(8,2)	Hours recorded
billable	BOOLEAN	Whether the hours are billable

Tablesemployeesdepartmentsprojectswork_logs