Description
Issue: Add New SQL & Pandas Problems
Description
I propose we expand our repository with two new questions that require solutions in both SQL and Pandas. One question will be challenging, and the other will be a simpler problem. These questions will help users practice a range of skills from advanced data analysis to basic data filtering.
Problem 1: Employee Performance Analysis (Challenging)
Context:
A company maintains a database of its employees and their monthly performance metrics. The data is stored in two tables: Employees
and Performance
.
Table: Employees
Column Name | Type | Description |
---|---|---|
employee_id | INT | Unique identifier for each employee. |
name | VARCHAR | Employee name. |
department | VARCHAR | Department of the employee. |
Table: Performance
Column Name | Type | Description |
---|---|---|
employee_id | INT | Foreign key referencing Employees.employee_id . |
month | DATE | The month of the performance record (assume the first day of the month). |
sales | INT | Number of sales made by the employee in that month. |
targets_met | INT | Number of targets met by the employee in that month. |
Task:
Write a solution that reports, for each department:
- Employee Count: Total number of employees.
- Average Monthly Sales: Average monthly sales per employee (across all available months).
- Target Achievement Rate: Overall target achievement rate defined as:
[
\text{Target Achievement Rate} = \frac{\text{Total Targets Met}}{\text{Total Targets Assigned}}
]
(Assume each employee is assigned a fixed target of 10 per month.)
Return a table with columns:
department
, employee_count
, avg_monthly_sales
, target_achievement_rate
.
Requirements:
- SQL: Write a query using appropriate JOINs, GROUP BY, and aggregation (with rounding).
- Pandas: Write a function that takes two DataFrames (one for
Employees
and one forPerformance
) and returns the result DataFrame.
Example Output (Simplified):
department | employee_count | avg_monthly_sales | target_achievement_rate |
---|---|---|---|
IT | 5 | 250.75 | 0.92 |
Finance | 3 | 180.50 | 0.88 |
HR | 2 | 200.00 | 0.95 |
Problem 2: List HR Department Employees (Simple)
Context:
A company wants to quickly list all employees who work in the HR department.
Table: Employees
Column Name | Type | Description |
---|---|---|
employee_id | INT | Unique identifier for each employee. |
name | VARCHAR | Employee name. |
Salary | INT | Employee salary. |
Department | VARCHAR | Department of the employee. |
Task:
Write a solution to return the employee_id
and name
of employees who belong to the HR department.
Requirements:
- SQL: Write a query using a simple
WHERE
clause. - Pandas: Write a function that filters the DataFrame for the HR department.
Example Output:
employee_id | name |
---|---|
1 | John Doe |
6 | Alice |
Note: Adjust sample output as needed based on your data.
Additional Notes
- For each problem, ensure the SQL solution uses proper formatting and functions (e.g.,
ROUND()
for numeric values) and that the Pandas solution uses operations likemerge
,groupby
, and filtering. - These problems will help users practice both advanced and basic techniques in SQL and Python (Pandas).
Please share your feedback or suggest any modifications to these proposed problems.