Straive FTE Hiring | Data Analyst | SQL Question | On-Campus

I’d recently given the Straive OA for the role of Data Analyst as part of its on‑campus FTE drive for the same role, and I’d thought I’d share the question for anyone preparing for this role in the future.

Problem Description

Given two tables, Employee and Department, write a query to compute two metrics for each department:

1. The exact count of employees working in that department.
2. The cumulative count of employees up to and including that department, calculated as a running total over an unbounded preceding window sorted by dep_id.

Input Tables

Department

Column Name	Type	Description
dep_id	INT	Primary key
dep_name	VARCHAR	Name of the department

Employee

Column Name	Type	Description
emp_id	INT	Primary key
dep_id	INT	Foreign key referencing Department.dep_id
emp_name	VARCHAR	Employee name
salary	INT	Employee salary

Approach

1. Join & CTE: I started out by creating a CTE using WITH. The CTE consisted of an INNER JOIN on the Department and the Employee table on the department_id. I then grouped by department_id and selected the department_id, the department_name as well as the COUNT of all employees inside the department (using COUNT(DISTINCT) to account for duplicate rows).
2. Window Functions: I then used the window function SUM(employee_count) OVER (...) ordered by dep_id with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to generate the running cumulative total across departments.

Time

We were given 15 minutes for this particular problem (it should be enough if one has done LeetCode's SQL 50). The rest of the OA consisted of mostly MCQs on general aptitude, ML and Cloud Computing. It also consisted of a DSA question on arrays, which was similar to Maximum Subarray.

I hope this helps anyone prepping for this role in the future.