Guided Exercises - Stored Procedures: Part-2 - MySQL - More Practical Use Cases

[akash-coded]

Let's continue to explore more complex scenarios with stored procedures in the MySQL database, particularly in the context of the classicmodels database. Each example here is non-repetitive, unique, and practical.

Exercise 1: Customer Segmentation for Marketing Campaigns

Context and Problem Statement:
Imagine you are tasked with segmenting customers for targeted marketing campaigns. The segmentation needs to consider:

1. The total spending of each customer.
2. The categories of products the customer usually buys.

Approach 1:

DELIMITER //
CREATE PROCEDURE SegmentCustomers()
BEGIN
  DROP TEMPORARY TABLE IF EXISTS CustomerSegmentation;
  CREATE TEMPORARY TABLE CustomerSegmentation(
    customerNumber INT,
    totalSpent DECIMAL(15,2),
    favoriteCategory VARCHAR(50),
    segment VARCHAR(50)
  );

  -- Populate table with total spending and favorite category
  INSERT INTO CustomerSegmentation(customerNumber, totalSpent, favoriteCategory)
  SELECT c.customerNumber, 
         SUM(p.amount),
         (SELECT categoryName 
          FROM products AS pr
          JOIN productlines AS pl ON pr.productLine = pl.productLine
          JOIN orderdetails AS od ON pr.productCode = od.productCode
          JOIN orders AS o ON od.orderNumber = o.orderNumber
          WHERE o.customerNumber = c.customerNumber
          GROUP BY categoryName
          ORDER BY SUM(od.quantityOrdered) DESC LIMIT 1)
  FROM customers AS c
  JOIN orders AS o ON c.customerNumber = o.customerNumber
  JOIN payments AS p ON c.customerNumber = p.customerNumber
  GROUP BY c.customerNumber;

  -- Assign segments based on conditions
  UPDATE CustomerSegmentation
  SET segment = CASE 
    WHEN totalSpent > 10000 AND favoriteCategory = 'Planes' THEN 'HighValue-Aviation'
    WHEN totalSpent > 10000 AND favoriteCategory = 'Cars' THEN 'HighValue-Automobile'
    WHEN totalSpent < 1000 THEN 'LowValue'
    ELSE 'General'
  END;

  -- Your code for using this segmentation goes here.
  -- e.g., Insert this data into a permanent table, or use it for sending targeted marketing emails
END;
//
DELIMITER ;

To run the procedure: CALL SegmentCustomers();

Exercise 2: Inventory Restocking Alert System

Context and Problem Statement:
Your company wants to have an automated way of checking which products need restocking. A product needs restocking if:

1. There are pending orders for the product.
2. The quantity in stock is less than 100.

Solution:

DELIMITER //
CREATE PROCEDURE CheckRestock()
BEGIN
  DROP TEMPORARY TABLE IF EXISTS RestockAlerts;
  CREATE TEMPORARY TABLE RestockAlerts(
    productCode VARCHAR(15),
    quantityInStock INT,
    quantityOrdered INT,
    restockNeeded BOOLEAN
  );

  -- Populate RestockAlerts
  INSERT INTO RestockAlerts(productCode, quantityInStock, quantityOrdered)
  SELECT p.productCode, p.quantityInStock, IFNULL(SUM(od.quantityOrdered),0)
  FROM products AS p
  LEFT JOIN orderdetails AS od ON p.productCode = od.productCode
  WHERE od.orderNumber IN (SELECT orderNumber FROM orders WHERE status = 'In Process')
  GROUP BY p.productCode;

  -- Update restockNeeded
  UPDATE RestockAlerts
  SET restockNeeded = CASE 
    WHEN quantityInStock < 100 AND quantityOrdered > 0 THEN TRUE
    ELSE FALSE
  END;

  -- Now you can use this data to send restocking alerts
  -- e.g., insert this into a permanent alert table or integrate with an alerting system
END;
//
DELIMITER ;

To run the procedure: CALL CheckRestock();

These are comprehensive solutions that could run in the context of the classicmodels database. They are intended to solve complex, real-world problems you might encounter in data engineering tasks.

Let's continue to build on the complexity of scenarios involving stored procedures in the MySQL database using the classicmodels database as a reference.

Exercise 3: Calculate Monthly Revenue and Growth

Context and Problem Statement:

Imagine you're working in a financial analytics team, and you need to calculate the monthly revenue for the previous year and the growth compared to the previous month. The goal is to identify which months had an unusual spike or drop in sales for further investigation.

Solution 1:

Here's how you can write a stored procedure to calculate the monthly revenue:

DELIMITER //
CREATE PROCEDURE CalculateMonthlyRevenue()
BEGIN
    DROP TEMPORARY TABLE IF EXISTS MonthlyRevenue;
    CREATE TEMPORARY TABLE MonthlyRevenue (
        yearMonth CHAR(7),
        revenue DECIMAL(15, 2),
        growth DECIMAL(5, 2)
    );

    -- Populate table with revenue
    INSERT INTO MonthlyRevenue(yearMonth, revenue)
    SELECT DATE_FORMAT(orderDate, '%Y-%m') AS yearMonth,
           SUM(quantityOrdered * priceEach) AS revenue
    FROM orders
    JOIN orderdetails USING(orderNumber)
    WHERE YEAR(orderDate) = YEAR(CURDATE()) - 1
    GROUP BY yearMonth;

    -- Calculate growth
    UPDATE MonthlyRevenue
    SET growth = (revenue / LAG(revenue, 1, NULL) OVER (ORDER BY yearMonth) - 1) * 100;

    -- Code for using this data for further analytics or reporting
END;
//
DELIMITER ;

Solution 2: Using Views for Easier Querying

In this alternative approach, we'll use a view to calculate monthly revenue and growth, so that other team members can query it more easily.

-- Create the view
CREATE VIEW MonthlyRevenueView AS
SELECT DATE_FORMAT(orderDate, '%Y-%m') AS yearMonth,
       SUM(quantityOrdered * priceEach) AS revenue
FROM orders
JOIN orderdetails USING(orderNumber)
WHERE YEAR(orderDate) = YEAR(CURDATE()) - 1
GROUP BY yearMonth;

-- Create Stored Procedure
DELIMITER //
CREATE PROCEDURE CalculateMonthlyRevenueUsingView()
BEGIN
    DROP TEMPORARY TABLE IF EXISTS MonthlyRevenueFromView;
    CREATE TEMPORARY TABLE MonthlyRevenueFromView (
        yearMonth CHAR(7),
        revenue DECIMAL(15, 2),
        growth DECIMAL(5, 2)
    );

    -- Populate table with revenue from view
    INSERT INTO MonthlyRevenueFromView(yearMonth, revenue)
    SELECT * FROM MonthlyRevenueView;

    -- Calculate growth as before
    UPDATE MonthlyRevenueFromView
    SET growth = (revenue / LAG(revenue, 1, NULL) OVER (ORDER BY yearMonth) - 1) * 100;

    -- Code for using this data for further analytics or reporting
END;
//
DELIMITER ;

To run either procedure, use the command: CALL CalculateMonthlyRevenue(); or CALL CalculateMonthlyRevenueUsingView();

These exercises and solutions offer a deep dive into complex MySQL stored procedures, providing various approaches to address real-world data engineering challenges. The complexity lies not just in the SQL syntax and features used, but also in understanding the business problems these procedures are designed to solve.