DEV Community: MOHAMMAD SHAHBAZ ALAM

SQL Case Study 2: Human Resources

MOHAMMAD SHAHBAZ ALAM — Fri, 02 Jun 2023 05:21:19 +0000

In the world of data management and analysis, SQL (Structured Query Language) plays a vital role in handling and querying vast amounts of information stored in databases. In this case study, we will explore the application of SQL in the context of Human Resources (HR). By leveraging SQL, we can effectively manage employee data, analyze departmental information, and gain valuable insights into the workforce. Let’s dive into the case study and see how SQL can be utilized to tackle HR-related challenges.

Overview of the Data:

To begin with, we have three tables: ‘departments’, ‘employees’, and ‘projects’.The ‘departments’ table contains information about different departments, including the department’s ID, name, and manager’s ID. The ‘employees’ table stores data on individual employees, such as their ID, name, hire date, job title, and the department they belong to. Lastly, the ‘projects’ table contains details about various projects, including the project’s ID, name, start date, end date, and associated department.

The challenge for today is provided by Data in Motion and you can find the challenge here.

Let's create a database:

CREATE DATABASE departmentsDB;

Let’s create the table in our database:

-- Create 'departments' table
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

-- Create 'employees' table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    hire_date DATE,
    job_title VARCHAR(50),
    department_id INT REFERENCES departments(id)
);

-- Create 'projects' table
CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    start_date DATE,
    end_date DATE,
    department_id INT REFERENCES departments(id)
);

Now, let’s enter data into tables:

-- Insert data into 'departments'
INSERT INTO departments (name, manager_id)
VALUES ('HR', 1), ('IT', 2), ('Sales', 3);

-- Insert data into 'employees'
INSERT INTO employees (name, hire_date, job_title, department_id)
VALUES ('John Doe', '2018-06-20', 'HR Manager', 1),
       ('Jane Smith', '2019-07-15', 'IT Manager', 2),
       ('Alice Johnson', '2020-01-10', 'Sales Manager', 3),
       ('Bob Miller', '2021-04-30', 'HR Associate', 1),
       ('Charlie Brown', '2022-10-01', 'IT Associate', 2),
       ('Dave Davis', '2023-03-15', 'Sales Associate', 3);

-- Insert data into 'projects'
INSERT INTO projects (name, start_date, end_date, department_id)
VALUES ('HR Project 1', '2023-01-01', '2023-06-30', 1),
       ('IT Project 1', '2023-02-01', '2023-07-31', 2),
       ('Sales Project 1', '2023-03-01', '2023-08-31', 3);

Update ‘manager_id’ in the ‘departments’ table:

-- Update 'manager_id' in 'departments'
UPDATE departments
SET manager_id = (SELECT id FROM employees WHERE name = 'John Doe')
WHERE name = 'HR';

UPDATE departments
SET manager_id = (SELECT id FROM employees WHERE name = 'Jane Smith')
WHERE name = 'IT';

UPDATE departments
SET manager_id = (SELECT id FROM employees WHERE name = 'Alice Johnson')
WHERE name = 'Sales';

Now, let’s try to answer the questions with the help of the above data.

Questions:

Find the longest ongoing project for each department.

SELECT dept.name, proj.name, MAX(end_date - start_date) AS duration
 FROM projects proj
JOIN departments dept ON proj.department_id = dept.id
GROUP BY dept.name, proj.name
ORDER BY duration DESC;

The query retrieves the department name, project name, and the maximum duration (in days) for each project in each department. The results are ordered in descending order of duration, allowing us to identify the project with the longest ongoing duration in each department.

Find all employees who are not managers.

SELECT *
FROM employees
WHERE id NOT IN(SELECT manager_id FROM departments);

This query selects all rows from the ‘employees’ table where the ‘id’ of the employee is not present in the ‘manager_id’ column of the ‘departments’ table.

** Find all employees who have been hired after the start of a project in their department.**

SELECT emp.name, proj.start_date, emp.hire_date
FROM employees emp
JOIN projects proj ON emp.department_id = proj.department_id
WHERE emp.hire_date > proj.start_date;

This query joins the ‘employees’ and ‘projects’ tables based on the department_id column. It selects the employee name, hire date, department ID, project name, and project start date. The WHERE clause filters the results to include only employees who were hired after the start date of a project in their department.

4.** Rank employees within each department based on their hire date (the earliest hire gets the highest rank).**

SELECT emp.name AS employee_name, emp.hire_date, emp.department_id,
    RANK() OVER(PARTITION BY emp.department_id ORDER BY emp.hire_date ASC) AS rank
FROM employees emp
ORDER BY emp.department_id, rank;

This query uses the RANK() function along with the OVER clause to rank employees within each department based on their hire date. The PARTITION BY clause divides the employees into partitions based on the department_id, and the ORDER BY clause orders the employees within each partition by their hire_date in ascending order.

Find the duration between the hire date of each employee and the hire date of the next employee hired in the same department.

WITH ranked_employees AS (
    SELECT emp.name AS employee_name, emp.hire_date, emp.department_id,
        RANK() OVER(PARTITION BY emp.department_id ORDER BY emp.hire_date ASC) AS rank_num
    FROM employees emp
),
lead_ranked AS (
    SELECT *,
        LEAD(hire_date) OVER(PARTITION BY department_id ORDER BY hire_date ASC) AS next_hire_date
    FROM ranked_employees
)

SELECT re1.department_id, re1.employee_name AS employee, re2.employee_name AS next_hired_employee,
    re2.hire_date - re1.hire_date AS duration
FROM lead_ranked re1
JOIN lead_ranked re2 ON re1.rank_num = re2.rank_num - 1 AND re1.department_id = re2.department_id;

This query calculates the duration between the hire date of each employee and the hire date of the next employee hired in the same department. The results include the department ID, employee name, next hired employee name, and the duration between their hire dates.

Conclusions:

SQL proves to be a powerful tool for managing HR-related data. In this case study, we explored various applications of SQL, including identifying the longest ongoing project per department, identifying non-manager employees, ranking employees by hire date, and calculating durations between hire dates. By harnessing the capabilities of SQL, HR professionals can optimize workforce management, gain valuable insights, and make data-driven decisions that contribute to the overall success of the organization.

SQL Case Study - Tiny Shop Sales

MOHAMMAD SHAHBAZ ALAM — Thu, 25 May 2023 05:01:52 +0000

This case study focuses on utilizing PostgreSQL, a popular relational database management system, to analyze sales data for Tiny Shop. By leveraging various SQL functionalities such as aggregations, CASE WHEN statements, window functions, joins, date-time functions, and Common Table Expressions (CTEs), we aim to extract valuable insights and answer key questions related to product prices, customer orders, revenue, and more.

Overview of the Data:

The Tiny Shop sales database contains tables such as Products, Customers, Orders, and Order_Items. The Products table includes information about the products, including their prices. The Customers table stores customer details, while the Orders table contains order-specific information. The Order_Items table links the orders with the products and includes quantity information.

The challenge for today is provided by Data in Motion and you can find the dataset here SQL Case Study 1: Tiny Shop Sales — Data in Motion (d-i-motion.com)

CREATE DATABASE SalesDB;

Lets create table in our database:

-- Customer Table
CREATE TABLE customers (
    customer_id integer PRIMARY KEY,
    first_name varchar(100),
    last_name varchar(100),
    email varchar(100)
);

-- Products Table
CREATE TABLE products (
    product_id integer PRIMARY KEY,
    product_name varchar(100),
    price decimal
);

-- Orders Table
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    customer_id integer,
    order_date date
);

-- Order Items Table 
CREATE TABLE order_items (
    order_id integer,
    product_id integer,
    quantity integer
);

Lets insert data into table

-- Customers Table
INSERT INTO customers (customer_id, first_name, last_name, email) VALUES
(1, 'John', 'Doe', 'johndoe@email.com'),
(2, 'Jane', 'Smith', 'janesmith@email.com'),
(3, 'Bob', 'Johnson', 'bobjohnson@email.com'),
(4, 'Alice', 'Brown', 'alicebrown@email.com'),
(5, 'Charlie', 'Davis', 'charliedavis@email.com'),
(6, 'Eva', 'Fisher', 'evafisher@email.com'),
(7, 'George', 'Harris', 'georgeharris@email.com'),
(8, 'Ivy', 'Jones', 'ivyjones@email.com'),
(9, 'Kevin', 'Miller', 'kevinmiller@email.com'),
(10, 'Lily', 'Nelson', 'lilynelson@email.com'),
(11, 'Oliver', 'Patterson', 'oliverpatterson@email.com'),
(12, 'Quinn', 'Roberts', 'quinnroberts@email.com'),
(13, 'Sophia', 'Thomas', 'sophiathomas@email.com');

-- Products Table
INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Product A', 10.00),
(2, 'Product B', 15.00),
(3, 'Product C', 20.00),
(4, 'Product D', 25.00),
(5, 'Product E', 30.00),
(6, 'Product F', 35.00),
(7, 'Product G', 40.00),
(8, 'Product H', 45.00),
(9, 'Product I', 50.00),
(10, 'Product J', 55.00),
(11, 'Product K', 60.00),
(12, 'Product L', 65.00),
(13, 'Product M', 70.00);

-- Orders Table
INSERT INTO orders (order_id, customer_id, order_date) VALUES
(1, 1, '2023-05-01'),
(2, 2, '2023-05-02'),
(3, 3, '2023-05-03'),
(4, 1, '2023-05-04'),
(5, 2, '2023-05-05'),
(6, 3, '2023-05-06'),
(7, 4, '2023-05-07'),
(8, 5, '2023-05-08'),
(9, 6, '2023-05-09'),
(10, 7, '2023-05-10'),
(11, 8, '2023-05-11'),
(12, 9, '2023-05-12'),
(13, 10, '2023-05-13'),
(14, 11, '2023-05-14'),
(15, 12, '2023-05-15'),
(16, 13, '2023-05-16');

-- Order Items
INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1, 1, 2),
(1, 2, 1),
(2, 2, 1),
(2, 3, 3),
(3, 1, 1),
(3, 3, 2),
(4, 2, 4),
(4, 3, 1),
(5, 1, 1),
(5, 3, 2),
(6, 2, 3),
(6, 1, 1),
(7, 4, 1),
(7, 5, 2),
(8, 6, 3),
(8, 7, 1),
(9, 8, 2),
(9, 9, 1),
(10, 10, 3),
(10, 11, 2),
(11, 12, 1),
(11, 13, 3),
(12, 4, 2),
(12, 5, 1),
(13, 6, 3),
(13, 7, 2),
(14, 8, 1),
(14, 9, 2),
(15, 10, 3),
(15, 11, 1),
(16, 12, 2),
(16, 13, 3);

QUESTIONS

--1. Which product has the highest price? Only return a single row.

To determine the product with the highest price, a simple query using the MAX function on the price column of the Products table can provide the desired result.

SELECT 
  product_name, 
  price
FROM products
ORDER BY price DESC
LIMIT 1;

--2. Which customer has made the most orders?

By utilizing an aggregation function like COUNT, we can group the orders by customer and retrieve the customer with the highest count.

SELECT 
  customers.first_name, 
  customers.last_name, 
  COUNT(orders.order_id) AS order_count
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.first_name, customers.last_name
ORDER BY order_count DESC
LIMIT 1;

--3. What's the total revenue per product?

To calculate the total revenue per product, we can use joins and aggregations to combine the necessary tables and calculate the sum of the revenue for each product.

SELECT 
   pro.product_name, 
   SUM(pro.price * items.quantity) as total_revenue
FROM products pro
JOIN order_items items ON pro.product_id = items.product_id
GROUP BY pro.product_name
ORDER BY total_revenue DESC;

--4. Find the day with the highest revenue.

The SQL query retrieves the order date and the total revenue for each order, calculated by multiplying the price of each product with its corresponding quantity.

SELECT  
  ord.order_date,  
  SUM(pro.price * items.quantity) total_revenue
FROM products pro
JOIN order_items items ON pro.product_id = items.product_id
JOIN orders ord ON items.order_id = ord.order_id
GROUP BY ord.order_date
ORDER BY total_revenue DESC
LIMIT 1;

--5. Find the first order (by date) for each customer.

The SQL query retrieves the customer details of each customer, along with the minimum order date (min(ord.order_date)) for each customer. The results are then grouped by the customer's first name, last name, and order date. Finally, the results are sorted in ascending order based on the first order date.

SELECT  
  cus.first_name, 
  cus.last_name, 
  min(ord.order_date) first_order
FROM customers cus
JOIN orders ord ON cus.customer_id = ord.customer_id
GROUP  BY cus.first_name, cus.last_name, ord.order_date
ORDER BY first_order;

--6) Find the top 3 customers who have ordered the most distinct products

The SQL query retrieves the customer details of each customer, along with the count of distinct product names ordered by each customer.

SELECT 
   cust.first_name, 
   cust.last_name, 
   COUNT(DISTINCT product_name) unique_products
FROM customers cust
JOIN orders ord ON cust.customer_id = ord.customer_id
JOIN order_items items ON ord.order_id = items.order_id
JOIN products prod ON items.product_id = prod.product_id
GROUP BY cust.first_name, cust.last_name
ORDER BY unique_products DESC
LIMIT 3;

--7. Which product has been bought the least in terms of quantity?

The SQL query retrieves the product ID and the sum of quantities for each product. The results are then grouped by the product ID and ordered in ascending order based on the total quantities.

SELECT  
   prod.product_id, 
   SUM(items.quantity) Total_Quantities
FROM order_items items
JOIN products prod ON items.product_id = prod.product_id
GROUP BY prod.product_id
ORDER BY Total_Quantities
LIMIT 3;

--8) What is the median order total?

Using window functions and aggregations, we can calculate the median order total by sorting the orders by total and selecting the middle value.

WITH order_totals AS (
    SELECT 
       ord.order_id, 
       SUM(prod.price * items.quantity) AS total
    FROM orders ord
    JOIN order_items items ON ord.order_id = items.order_id
    JOIN products prod ON items.product_id = prod.product_id
    GROUP BY ord.order_id
)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS median_order_total
FROM order_totals;

--9) For each order, determine if it was ‘Expensive’ (total over 300), ‘Affordable’ (total over 100), or ‘Cheap’.

Using CASE WHEN statements, we can classify each order based on its total amount into categories like 'Expensive', 'Affordable', or 'Cheap'.

 SELECT 
   order_id,
      CASE
         WHEN revenue > 300 THEN 'Expensive'
         WHEN revenue > 100 THEN 'Affordable'
         ELSE 'Cheap'
      END AS price_bracket
      FROM (
       SELECT 
         order_id, 
         sum((price * quantity)) as revenue
      FROM products prod
LEFT JOIN order_items items ON prod.product_id = items.product_id
GROUP BY order_id
) as total_order;

--10) Find customers who have ordered the product with the highest price.

Through joins and subqueries, we can retrieve the customers who have ordered the product with the highest price by matching the product's price with the orders.

SELECT 
   customer_id, 
   cust.first_name, 
   cust.last_name
FROM customers cust
LEFT JOIN orders
USING (customer_id)
LEFT JOIN order_items items
USING (order_id)
LEFT JOIN products prod
USING (product_id)
WHERE price = (
 SELECT MAX(price)
 FROM products
);

Conclusion:

This case study highlights the practical application of PostgreSQL and SQL functionalities in analyzing sales data for Tiny Shop. By utilizing various SQL techniques, we can gain valuable insights into product pricing, customer behavior, revenue analysis, and order categorization. The versatility of PostgreSQL empowers businesses to extract meaningful information from their data, enabling informed decision-making and facilitating the growth and success of their operations.