You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 for Performance) 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 like merge, 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.
The text was updated successfully, but these errors were encountered:
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
andPerformance
.Table: Employees
Table: Performance
Employees.employee_id
.Task:
Write a solution that reports, for each department:
[
\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:
Employees
and one forPerformance
) and returns the result DataFrame.Example Output (Simplified):
Problem 2: List HR Department Employees (Simple)
Context:
A company wants to quickly list all employees who work in the HR department.
Table: Employees
Task:
Write a solution to return the
employee_id
andname
of employees who belong to the HR department.Requirements:
WHERE
clause.Example Output:
Note: Adjust sample output as needed based on your data.
Additional Notes
ROUND()
for numeric values) and that the Pandas solution uses operations likemerge
,groupby
, and filtering.Please share your feedback or suggest any modifications to these proposed problems.
The text was updated successfully, but these errors were encountered: