Finance · Beginner
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 workspace
Open in Python Lab
How to do this projectdata/ folder of the Python Lab.You 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
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.
Load both files. Confirm the headcount, the departments and the counties, and check the salary column for anything odd before you trust it.
Find the highest earners overall and within each department. Be precise about ties: two people on the same salary share a rank.
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.
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.
Two 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.
The scenario
Objectives
Tasks and business questions
Dataset and data dictionaryemployees420 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 |
departmentsOne 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 |
22 exercises worth 360 points. Your query runs against this project’s dataset and is marked automatically.
7 exercises worth 110 points. Your code runs in the Python Lab against this project’s data and is marked automatically.
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.
All graded SQL exercises passed in the SQL workspaceannual_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 |