Finance · Beginner
Kenya Employee Salary Analysis
Answer a CEO's pay questions from a 420-person payroll: who earns what, where the money goes, and which departments are over budget.
Open in SQL workspaceThe scenario
The scenarioYou are the new people analyst at Savanna Tech Group, a Nairobi company with 420 staff across eight departments and offices in five counties. The CEO has a board meeting in a week and wants straight answers from the payroll extract: headcount and pay by department, the highest earners, who is paid above their department's average, how salaries relate to tenure, and whether any department's salary bill has outgrown its budget. HR has given you two files: the employee register and a small departments table with each department's head, office and annual budget.
Your role
People Analyst, Savanna Tech Group
The business problem
Leadership sets pay by instinct and department heads argue from anecdotes. The company needs a clear, repeatable set of queries that show where salary money goes, which departments are outliers, and where the top earners sit, so that the next pay review is based on numbers rather than negotiation.
Objectives
Objectives- 1Filter, sort and aggregate a payroll table with SQL and pandas.
- 2Find the top earners with ORDER BY, OFFSET and window functions, and understand why 'second highest' has more than one correct approach.
- 3Compare individuals with group averages using subqueries and common table expressions.
- 4Join the register to the departments table to compare salary bills with budgets.
- 5Write CTEs that make a multi-step analysis readable, and explain why they beat nested subqueries.
Tasks and business questions
Tasks and business questions- 1
Know the register
Load both files. Confirm the headcount, the departments and the counties, and check the salary column for anything odd before you trust it.
- How many employees are there, and how many per department?
- What are the lowest, highest and median monthly salaries?
- 2
The top of the payroll
Find the highest earners overall and within each department. Be precise about ties: two people on the same salary share a rank.
- Who is the second highest paid employee, and what happens to your query if two people share the top salary?
- Who are the top three earners in each department?
- 3
Above and below the line
Compare each person with the company average and with their own department's average. Do it once with a subquery and once with a CTE, and notice which one you would rather maintain.
- How many employees earn more than the company average?
- Which employees earn more than their department's average, and by how much?
- 4
Budgets and the board
Join the register to the departments table. Turn monthly salaries into an annual bill and compare it with each department's budget. Write the CEO summary.
- Which departments spend more than 60 percent of their annual budget on salaries?
- What one change would you recommend, and which query supports it?
Dataset and data dictionary
Dataset and data dictionaryTwo CSV files. employees has 420 rows and 10 columns: employee id, full name, gender, department, job title, county, hire date, monthly salary in Kenyan shillings, age and a performance rating from 1 to 5. departments has one row per department with its head, annual budget in shillings, office county and founding year.
Employee registeremployees
420 employees of Savanna Tech Group with department, title, county, hire date, monthly salary and performance rating. · 420 rows
| Column | Type | Description | Example |
|---|---|---|---|
employee_id | integer | Unique employee number. | 1001 |
full_name | text | Employee's full name. | Mercy Wanjiru |
gender | text | Female or Male. | Female |
department | text | One of eight departments; joins to departments.department. | Engineering |
job_title | text | Current job title. | Data Analyst |
county | text | County where the employee is based. | Nairobi |
hire_date | date | Date the employee joined. | 2019-03-11 |
salary_kes | integer | Gross monthly salary in Kenyan shillings. | 145000 |
age | integer | Age in years. | 31 |
performance_rating | integer | Last appraisal score from 1 (lowest) to 5 (highest). | 4 |
Departmentsdepartments
One row per department: its head, annual budget, office county and founding year. · 8 rows
| Column | Type | Description | Example |
|---|---|---|---|
department | text | Department name; matches employees.department. | Finance |
head_of_department | text | Name of the department head. | Ruth Kamau |
annual_budget_kes | integer | Approved annual budget in shillings, salaries included. | 48000000 |
office | text | County of the department's main office. | Nairobi |
founded_year | integer | Year the department was created. | 2014 |
Graded SQL exercises
11 exercises worth 180 points. Your query runs against this project’s dataset and is marked automatically.
- Engineers based in NairobiBeginner10 pts
- Headcount by departmentBeginner10 pts
- Average salary by departmentBeginner10 pts
- The second highest paid employeeIntermediate15 pts
- Paid above the company averageIntermediate15 pts
- Departments averaging above 150,000Intermediate15 pts
- Top three earners in each departmentIntermediate20 pts
- Annual salary bill against budgetIntermediate20 pts
- CTE: paid above their own department's averageAdvanced20 pts
- CTE: salary bandsAdvanced20 pts
- Two CTEs: each department's share of the payrollAdvanced25 pts
Required deliverables
- All graded SQL exercises passed in the SQL workspace
- All graded Python exercises passed in the Python lab
- A one-page summary for the CEO: headcount and pay by department, the five highest earners, departments over budget, and one recommendation
All graded SQL exercises passed in the SQL workspaceHow it is marked
- Data cleaning15%
- Technical accuracy20%
- Analysis quality20%
- Visualisation and dashboard design15%
- Business insights15%
- Recommendations10%
- Documentation and presentation5%
- Pass mark60%
The exercises are marked automatically. The summary is marked on whether every figure can be reproduced from a query or a pandas expression, whether the department comparison accounts for headcount, and whether the recommendation follows from the numbers.