Skip to content

Employee Database

👋 Welcome to your workspace! Here, you can run SQL queries, write Python code, and add text in Markdown. This workspace is automatically connected to a MySQL database containing employee data for a fictional company (source).

You can click the "Browse tables" button in the upper righthand corner of the cell below to view the available tables.

There is a short query and a visualization of the average time an employee spends at the company (for those that left) by position rendered in Plotly to get you started.

Spinner
DataFrameas
employee_tenures
variable
WITH past_employees AS (
	SELECT 
    	emp_no,
        Min(from_date) AS start_date,
        Max(to_date) AS end_date
    FROM salaries
    WHERE to_date != (SELECT MAX(to_date) FROM salaries)
    GROUP BY emp_no),
    
employee_tenures AS (
	SELECT 
		emp_no,
        start_date,
        end_date,
        DATEDIFF(end_date, start_date) / 365 AS employee_tenure
    FROM past_employees)

SELECT 
	title,
    AVG(employee_tenure) AS avg_employee_tenure
FROM employee_tenures
INNER JOIN titles
    ON employee_tenures.emp_no = titles.emp_no
    AND employee_tenures.end_date = titles.to_date
GROUP BY title
ORDER BY avg_employee_tenure
# Import libraries
import pandas as pd
import plotly.express as px

# Create bar chart
fig = px.bar(employee_tenures, x="avg_employee_tenure", y="title")

# Create labels and show plot
fig.update_layout(
    title="Average Employee Tenure<br><sup>By Last Held Position</sup>",
    title_x=0.5,
    xaxis_title="Average Tenure",
    yaxis_title="Position",
    legend_title="Position",
    template="plotly_dark",
)
fig.show()

This is an interactive plot! Hover over the bars to get the precise average tenure of each position.

💪 Now it's your turn to construct your own queries and analyze the data! Remember, you can review the tables in the database at any point using the "Browse tables" button.