Complex Scenarios - Data Analysis Workflow with SQL - MySQL

[akash-coded]

Scenario 1: Streamlined Data Analysis Workflow with Window Functions and Stored Procedures

In many scenarios, organizations might need to generate complex analyses and reports on a regular basis. For such tasks, SQL window functions and stored procedures can help in creating streamlined and efficient workflows.

Consider a scenario where the management of ClassicModels wants a monthly report of the top 5 products (by sales) for each product line. They also want to know if these top products have improved their ranking over time.

Step 1: Data Preparation using Window Functions

First, we need to prepare the data for analysis. We will create a view that shows the product sales by month and their rank within their product line.

CREATE VIEW product_sales_ranking AS 
SELECT 
    p.productCode, 
    p.productLine, 
    DATE_FORMAT(o.orderDate, '%Y-%m') AS orderMonth, 
    SUM(od.quantityOrdered * od.priceEach) AS sales,
    RANK() OVER (
        PARTITION BY p.productLine, DATE_FORMAT(o.orderDate, '%Y-%m') 
        ORDER BY SUM(od.quantityOrdered * od.priceEach) DESC
    ) as rankInProductLine
FROM 
    products p
JOIN 
    orderdetails od ON p.productCode = od.productCode
JOIN 
    orders o ON od.orderNumber = o.orderNumber
GROUP BY 
    p.productCode, p.productLine, DATE_FORMAT(o.orderDate, '%Y-%m');

Step 2: Analysis Workflow with Stored Procedure

Next, we create a stored procedure to extract the top 5 products for each product line for a given month.

DELIMITER //

CREATE PROCEDURE GetTopProducts(IN target_month CHAR(7))
BEGIN
    SELECT 
        productLine, 
        productCode, 
        sales, 
        rankInProductLine
    FROM 
        product_sales_ranking 
    WHERE 
        orderMonth = target_month AND 
        rankInProductLine <= 5;
END //

DELIMITER ;

The team can now generate a monthly report by simply calling this stored procedure with the target month as an argument. The procedure will provide a list of top products, their sales, and their rankings within their product lines, helping the team to track and analyze product performance effectively.

CALL GetTopProducts('2023-07');

Again, this example shows how advanced SQL features can be integrated into complex data analysis workflows. Using window functions, we can perform complex calculations on data subsets, and stored procedures allow us to encapsulate and reuse those computations.

Please note, the examples shown here are simplified for clarity. Real-world use-cases would potentially involve more complex SQL queries, additional error handling, and further optimization techniques.

Scenario 2: Inventory Management with Rolling Averages

In inventory management, it is crucial to know how fast products are moving out of the inventory. This is usually done by calculating a rolling average of sales over a certain period. In this scenario, ClassicModels wants to calculate the 3-month rolling average of sales quantity for each product to get insights into inventory turnover and product popularity.

Step 1: Monthly Sales Calculation with Window Functions

First, we need to calculate the monthly sales for each product. We will create a view that represents these computations.

CREATE VIEW product_monthly_sales AS 
SELECT 
    p.productCode, 
    DATE_FORMAT(o.orderDate, '%Y-%m') AS salesMonth, 
    SUM(od.quantityOrdered) AS salesQuantity
FROM 
    products p
JOIN 
    orderdetails od ON p.productCode = od.productCode
JOIN 
    orders o ON od.orderNumber = o.orderNumber
GROUP BY 
    p.productCode, DATE_FORMAT(o.orderDate, '%Y-%m');

Step 2: Rolling Average Calculation

Now, we can calculate the 3-month rolling average for each product using the AVG() window function.

SELECT 
    productCode, 
    salesMonth, 
    AVG(salesQuantity) OVER (
        PARTITION BY productCode 
        ORDER BY STR_TO_DATE(salesMonth, '%Y-%m') 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rollingAvgSales
FROM 
    product_monthly_sales;

This will create a list of products with their 3-month rolling average of sales quantity. The window function AVG() calculates the average for each product (PARTITION BY productCode) over the window of the current month and the two preceding months (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).

This rolling average provides a smoother trend of product sales over time, reducing the impact of temporary fluctuations. It helps ClassicModels to identify the fast-moving products and thus can help in inventory planning.

This scenario demonstrates how SQL window functions can assist in creating efficient solutions for complex real-world problems. By combining window functions with SQL constructs like views, we can perform complex computations and derive valuable insights from the data.

Remember, real-world scenarios may involve additional complexity and require further optimization and error handling, which is not covered in this simplified example.

Scenario 2: Advanced Customer Segmentation and Sales Forecasting

In this scenario, ClassicModels wants to perform customer segmentation based on the historical sales data, and then, use this segmentation to do sales forecasting. They want to segment customers into different tiers based on their cumulative purchase amounts and the frequency of purchases. They also want to analyze how sales trends vary across different customer segments.

This is a multi-step process, involving various SQL concepts like CTEs, window functions, and complex joins.

Step 1: Cumulative Purchase Amount and Frequency Calculation

First, we need to calculate the cumulative purchase amount and the frequency of purchases for each customer. We will use CTEs and window functions to do these computations.

WITH customer_purchases AS (
    SELECT 
        c.customerNumber, 
        o.orderNumber, 
        o.orderDate,
        SUM(od.quantityOrdered * od.priceEach) as purchaseAmount
    FROM 
        customers c
    JOIN 
        orders o ON c.customerNumber = o.customerNumber
    JOIN 
        orderdetails od ON o.orderNumber = od.orderNumber
    GROUP BY 
        c.customerNumber, o.orderNumber, o.orderDate
),

customer_cumulative_frequency AS (
    SELECT 
        customerNumber, 
        orderDate,
        ROW_NUMBER() OVER (
            PARTITION BY customerNumber
            ORDER BY orderDate
        ) as purchaseFrequency,
        SUM(purchaseAmount) OVER (
            PARTITION BY customerNumber
            ORDER BY orderDate
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) as cumulativePurchaseAmount
    FROM 
        customer_purchases
)

SELECT * FROM customer_cumulative_frequency;

Step 2: Customer Segmentation

Based on the cumulative purchase amount and the frequency of purchases, we can segment customers into different tiers.

WITH customer_segments AS (
    SELECT 
        customerNumber, 
        CASE 
            WHEN cumulativePurchaseAmount > 100000 THEN 'High-value'
            WHEN cumulativePurchaseAmount > 50000 THEN 'Medium-value'
            ELSE 'Low-value'
        END as customerTier,
        purchaseFrequency
    FROM 
        customer_cumulative_frequency
)

SELECT * FROM customer_segments;

Step 3: Sales Forecasting

Now, we can analyze how sales trends vary across different customer segments and use this information to forecast future sales.

SELECT 
    cs.customerTier, 
    DATE_FORMAT(cp.orderDate, '%Y-%m') as salesMonth, 
    AVG(cp.purchaseAmount) as avgMonthlySales
FROM 
    customer_segments cs
JOIN 
    customer_purchases cp ON cs.customerNumber = cp.customerNumber
GROUP BY 
    cs.customerTier, DATE_FORMAT(cp.orderDate, '%Y-%m');

This will give us the average monthly sales for each customer tier, which we can use for sales forecasting.

This scenario demonstrates how SQL can be used in complex data analysis workflows to perform tasks like customer segmentation and sales forecasting. Window functions, CTEs, and complex joins are all instrumental in carrying out these tasks efficiently.

Remember, real-world scenarios may involve additional complexity and require further optimization and error handling, which is not covered in this simplified example.

Scenario 3: Advanced Sales Commission Calculation and Validation

Let's consider a complex data analysis workflow that involves the calculation of sales commissions for employees at ClassicModels. The sales commission is a percentage of the sales revenue that an employee has generated. However, it's not that simple. There are various rules and validations to be applied:

1. Sales commissions are only paid for orders that have been shipped.
2. Employees receive a higher percentage of commission if they are a senior staff member or have achieved a high level of sales.
3. There is a cap on the maximum amount of commission an employee can earn in a given month.

The SQL workflow to compute the commission might be as follows:

Step 1: Filter for Shipped Orders

We begin by filtering for orders that have been shipped.

WITH shipped_orders AS (
    SELECT 
        o.orderNumber,
        o.orderDate,
        od.productCode,
        od.quantityOrdered * od.priceEach AS revenue,
        c.salesRepEmployeeNumber
    FROM 
        orders o
    JOIN 
        orderdetails od ON o.orderNumber = od.orderNumber
    JOIN 
        customers c ON o.customerNumber = c.customerNumber
    WHERE 
        o.status = 'Shipped'
)

SELECT * FROM shipped_orders;

Step 2: Calculate Total Monthly Sales for Each Employee

Next, we calculate the total monthly sales for each employee.

WITH monthly_sales AS (
    SELECT 
        salesRepEmployeeNumber AS employeeNumber,
        DATE_FORMAT(orderDate, '%Y-%m') as salesMonth, 
        SUM(revenue) AS monthlySales
    FROM 
        shipped_orders
    GROUP BY 
        salesRepEmployeeNumber, DATE_FORMAT(orderDate, '%Y-%m')
)

SELECT * FROM monthly_sales;

Step 3: Determine Commission Rate Based on Employee Role and Sales

We determine the commission rate based on the employee's role and total sales. If the employee is a senior staff member or has generated high sales, they receive a higher commission rate.

WITH commission_rate AS (
    SELECT 
        e.employeeNumber,
        m.salesMonth,
        m.monthlySales,
        CASE
            WHEN e.jobTitle LIKE 'Sales Rep%' AND m.monthlySales > 15000 THEN 0.15
            WHEN e.jobTitle LIKE 'Sales Rep%' THEN 0.10
            WHEN e.jobTitle LIKE 'VP%' OR e.jobTitle LIKE 'Senior%' THEN 0.20
            ELSE 0.05
        END as commissionRate
    FROM 
        employees e
    JOIN 
        monthly_sales m ON e.employeeNumber = m.employeeNumber
)

SELECT * FROM commission_rate;

Step 4: Calculate Sales Commissions and Apply Cap

Finally, we calculate the sales commissions for each employee, applying a cap on the maximum commission amount.

SELECT 
    c.employeeNumber,
    c.salesMonth,
    c.monthlySales,
    c.commissionRate,
    LEAST(c.monthlySales * c.commissionRate, 2000) AS commission
FROM 
    commission_rate c;

This scenario demonstrates a complex, multi-step SQL workflow that involves advanced calculations, conditional logic, and data validations. Concepts like CTEs, JOINs, window functions, and aggregate functions are all crucial in carrying out these tasks efficiently. As always, real-world scenarios might involve additional complexities not covered in this simplified example.

Scenario 4: Advanced Stock Management

Let's assume a scenario that involves managing inventory at ClassicModels. The organization has several products with different quantities in stock, and they sell at various rates across the year. ClassicModels wants to have a proactive stock management system to prevent products from running out of stock.

The workflow is as follows:

1. Calculate the monthly sales for each product.
2. Calculate the rolling average of sales for the last three months for each product.
3. Flag any products that have less than two months' worth of stock left based on the rolling average sales.

Step 1: Calculate Monthly Sales

Calculate the total quantity sold for each product per month.

WITH monthly_sales AS (
    SELECT 
        productCode,
        DATE_FORMAT(orderDate, '%Y-%m') AS salesMonth,
        SUM(quantityOrdered) AS unitsSold
    FROM 
        orders o
    JOIN 
        orderdetails od ON o.orderNumber = od.orderNumber
    WHERE 
        status = 'Shipped'
    GROUP BY 
        productCode,
        DATE_FORMAT(orderDate, '%Y-%m')
)

SELECT * FROM monthly_sales;

Step 2: Calculate 3-Month Rolling Average Sales

Calculate the rolling average of sales for the last three months for each product.

WITH rolling_avg_sales AS (
    SELECT 
        productCode,
        salesMonth,
        AVG(unitsSold) OVER (
            PARTITION BY productCode 
            ORDER BY STR_TO_DATE(salesMonth, '%Y-%m') 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS avgUnitsSold
    FROM 
        monthly_sales
)

SELECT * FROM rolling_avg_sales;

Step 3: Flag Products with Low Stock

Flag any products that have less than two months' worth of stock left based on the rolling average sales.

SELECT 
    p.productCode,
    p.productName,
    p.quantityInStock,
    r.avgUnitsSold,
    (p.quantityInStock / r.avgUnitsSold) AS monthsOfStockLeft,
    CASE
        WHEN (p.quantityInStock / r.avgUnitsSold) < 2 THEN 'Low Stock'
        ELSE 'Sufficient Stock'
    END AS stockStatus
FROM 
    products p
JOIN 
    rolling_avg_sales r ON p.productCode = r.productCode
ORDER BY 
    monthsOfStockLeft;

In this scenario, we're using CTEs to structure the query in a logical, step-by-step manner, making it easier to understand and maintain. We're also using window functions to calculate the rolling average sales, which would be difficult to achieve with standard SQL aggregations. This example also showcases the combination of these advanced SQL concepts with conditional logic (CASE statement) to perform complex data analysis tasks.

Scenario 5: Monthly Report on Top Selling Categories

Let's imagine ClassicModels wants a comprehensive report at the end of each month that provides insights on their best selling product categories. This report should detail the total sales for each category, the most purchased product in that category, and the customer who made the largest purchase in the category. The data should be restricted to the most recent complete month and the report should only include categories that had sales in that month.

This is a complex, multi-step problem that involves multiple tables. It includes product categorization, order detail aggregation, and ranking within partitions. Here is how this might be broken down:

1. Identify the month of the most recent order.
2. Find total sales for each product category in that month.
3. Identify the most purchased product in each category in that month.
4. Identify the customer who made the largest purchase in each category in that month.

The SQL for this would be complex and could be developed step-by-step:

Step 1: Identify the month of the most recent order

First, we want to identify the month of the most recent order in our dataset.

SELECT DATE_FORMAT(MAX(orderDate), '%Y-%m') AS recentMonth
FROM orders;

Step 2: Find total sales for each product category in that month

Once we have our most recent month, we want to find the total sales for each category in that month.

WITH category_sales AS (
  SELECT 
      p.productLine, 
      DATE_FORMAT(o.orderDate, '%Y-%m') as salesMonth, 
      SUM(od.quantityOrdered * od.priceEach) as totalSales
  FROM 
      products p
  JOIN 
      orderdetails od ON p.productCode = od.productCode
  JOIN 
      orders o ON od.orderNumber = o.orderNumber
  WHERE 
      DATE_FORMAT(o.orderDate, '%Y-%m') = 'recentMonth'
  GROUP BY 
      p.productLine, DATE_FORMAT(o.orderDate, '%Y-%m')
)

SELECT * FROM category_sales;

Step 3: Identify the most purchased product in each category in that month

Next, we'll identify the most purchased product in each category for that month.

WITH most_purchased_product AS (
  SELECT 
      productLine, 
      productName,
      ROW_NUMBER() OVER (PARTITION BY productLine ORDER BY SUM(quantityOrdered) DESC) as row_num
  FROM 
      products p
  JOIN 
      orderdetails od ON p.productCode = od.productCode
  JOIN 
      orders o ON od.orderNumber = o.orderNumber
  WHERE 
      DATE_FORMAT(o.orderDate, '%Y-%m') = 'recentMonth'
  GROUP BY 
      productLine, productName
)

SELECT productLine, productName FROM most_purchased_product WHERE row_num = 1;

Step 4: Identify the customer who made the largest purchase in each category in that month

Finally, we'll identify the customer who made the largest purchase in each category for that month.

WITH top_category_customer AS (
  SELECT 
      p.productLine, 
      c.customerName,
      ROW_NUMBER() OVER (PARTITION BY p.productLine ORDER BY SUM(od.quantityOrdered * od.priceEach) DESC) as row_num
  FROM 
      products p
  JOIN 
      orderdetails od ON p.productCode = od.productCode
  JOIN 
      orders o ON od.orderNumber = o.orderNumber
  JOIN 
      customers c ON o.customerNumber = c.customerNumber
  WHERE 
      DATE_FORMAT(o.orderDate, '%Y-%m') = 'recentMonth'
  GROUP BY 
      p.productLine, c.customerName
)

SELECT productLine, customerName FROM top_category_customer WHERE row_num = 1;

In this scenario, we've performed a complex analysis workflow that involves aggregating sales data, ranking products and customers within product categories, and filtering data based on the date. We've used CTEs and window functions extensively, showcasing their ability to simplify complex queries and perform calculations that aren't possible with standard SQL aggregations.