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.

Spinner
DataFrameas
df
variable
select * 
from employees limit 1000