Complex Scenarios - Data Versioning in SQL - MySQL

[akash-coded]

Scenario 1: Tracking Price Changes

A typical requirement in data warehousing or data engineering is to handle slowly changing dimensions. This can refer to any entity whose data changes over time, but not in every transaction. Product prices are a good example.

ClassicModels needs to maintain a history of prices for its products. Currently, whenever a price changes, the new price simply overwrites the old one in the products table. But the company now wants to keep track of all historical prices, along with the date of change. The company also wants to maintain the current price in the products table for easy access.

To solve this scenario, we can maintain a product_price_history table which will store productCode, price, and the effective date of that price.

This table will be updated every time there is a price change in the products table. You could use a trigger on the products table to automate this.

Let's see the SQL commands for this:

1. First, let's create the product_price_history table:

CREATE TABLE product_price_history (
    productCode varchar(15) NOT NULL,
    price decimal(10,2) NOT NULL,
    effective_date date NOT NULL,
    PRIMARY KEY (productCode, effective_date),
    FOREIGN KEY (productCode) REFERENCES products(productCode)
);

2. Then, let's create a trigger that inserts a new row in product_price_history every time the price is updated in the products table:

CREATE TRIGGER price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.buyPrice <> OLD.buyPrice THEN
        INSERT INTO product_price_history (productCode, price, effective_date) 
        VALUES (NEW.productCode, NEW.buyPrice, CURDATE());
    END IF;
END;

Now, every time the price changes for a product, a new row will be inserted into the product_price_history table, preserving the history of price changes. This form of data versioning is particularly important in data warehousing scenarios where tracking changes over time is required.

3. To get the price history for a particular product:

SELECT productCode, price, effective_date
FROM product_price_history
WHERE productCode = 'S10_1678'
ORDER BY effective_date;

This scenario involves an understanding of triggers, which are SQL operations that are automatically performed when a specified database event occurs, such as an update to a table. This is a more advanced topic, but triggers are very useful in maintaining the integrity of the data in a database.

Scenario 2: Customer and their Orders Versioning

Consider a situation where ClassicModels company needs to maintain a history of customer's locations and the sales representative assigned to them. Just like the previous scenario, the current design simply overwrites the old data. They also want to keep track of all historical orders and the corresponding sales representative at that time of the order.

This scenario will involve maintaining two versioning tables customers_history and orders_history.

1. Creating history tables:

CREATE TABLE customers_history (
    customerNumber int(11) NOT NULL,
    addressLine1 varchar(50) NOT NULL,
    addressLine2 varchar(50) DEFAULT NULL,
    city varchar(50) NOT NULL,
    state varchar(50) DEFAULT NULL,
    salesRepEmployeeNumber int(11) DEFAULT NULL,
    effective_date date NOT NULL,
    PRIMARY KEY (customerNumber, effective_date),
    FOREIGN KEY (customerNumber) REFERENCES customers(customerNumber),
    FOREIGN KEY (salesRepEmployeeNumber) REFERENCES employees(employeeNumber)
);

CREATE TABLE orders_history (
    orderNumber int(11) NOT NULL,
    customerNumber int(11) NOT NULL,
    salesRepEmployeeNumber int(11) DEFAULT NULL,
    orderDate date NOT NULL,
    effective_date date NOT NULL,
    PRIMARY KEY (orderNumber, effective_date),
    FOREIGN KEY (orderNumber) REFERENCES orders(orderNumber),
    FOREIGN KEY (customerNumber) REFERENCES customers(customerNumber),
    FOREIGN KEY (salesRepEmployeeNumber) REFERENCES employees(employeeNumber)
);

2. Creating triggers:

CREATE TRIGGER customer_change
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    IF NEW.addressLine1 <> OLD.addressLine1 OR 
       NEW.addressLine2 <> OLD.addressLine2 OR 
       NEW.city <> OLD.city OR 
       NEW.state <> OLD.state OR
       NEW.salesRepEmployeeNumber <> OLD.salesRepEmployeeNumber THEN
        INSERT INTO customers_history (customerNumber, addressLine1, addressLine2, city, state, salesRepEmployeeNumber, effective_date) 
        VALUES (NEW.customerNumber, NEW.addressLine1, NEW.addressLine2, NEW.city, NEW.state, NEW.salesRepEmployeeNumber, CURDATE());
    END IF;
END;

CREATE TRIGGER order_change
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_history (orderNumber, customerNumber, salesRepEmployeeNumber, orderDate, effective_date) 
    VALUES (NEW.orderNumber, NEW.customerNumber, (SELECT salesRepEmployeeNumber FROM customers WHERE customerNumber = NEW.customerNumber), NEW.orderDate, CURDATE());
END;

Now, every time the customer's address or assigned sales representative changes, a new row will be inserted into the customers_history table. Similarly, when a new order is placed, a record will be added to the orders_history table with the sales representative at the time of the order.

3. Fetching history:

To get the customer's history, we can use:

SELECT * 
FROM customers_history 
WHERE customerNumber = 103
ORDER BY effective_date;

To get the history of orders and sales representatives:

SELECT * 
FROM orders_history 
WHERE customerNumber = 103
ORDER BY effective_date;

This versioning technique is known as Slowly Changing Dimension Type 2 (SCD Type 2) in data warehousing, where full history is preserved.

This scenario is an example of a real-world use case where maintaining the history of changes is crucial for accurate reporting and analysis. Such versioning tasks become very common and complex in large-scale data engineering workflows.

Scenario 3: Managing Inventory and Sales Data with Versioning

Imagine a situation where the ClassicModels company needs to keep track of the historical prices of each product as well as the inventory levels over time. In addition, they want to keep track of their sales with respect to the price at the time of each sale. Currently, any changes to the product price or quantity in stock overwrites the existing data. This scenario will involve maintaining two versioning tables product_history and order_details_history.

1. Creating history tables:

CREATE TABLE product_history (
    productCode varchar(15) NOT NULL,
    buyPrice decimal(10,2) NOT NULL,
    quantityInStock int(11) NOT NULL,
    effective_date date NOT NULL,
    PRIMARY KEY (productCode, effective_date),
    FOREIGN KEY (productCode) REFERENCES products(productCode)
);

CREATE TABLE order_details_history (
    orderNumber int(11) NOT NULL,
    productCode varchar(15) NOT NULL,
    quantityOrdered int(11) NOT NULL,
    priceEach decimal(10,2) NOT NULL,
    orderLineNumber int(11) NOT NULL,
    effective_date date NOT NULL,
    PRIMARY KEY (orderNumber, productCode, effective_date),
    FOREIGN KEY (orderNumber) REFERENCES orders(orderNumber),
    FOREIGN KEY (productCode) REFERENCES products(productCode)
);

2. Creating triggers:

CREATE TRIGGER product_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.buyPrice <> OLD.buyPrice OR 
       NEW.quantityInStock <> OLD.quantityInStock THEN
        INSERT INTO product_history (productCode, buyPrice, quantityInStock, effective_date) 
        VALUES (NEW.productCode, NEW.buyPrice, NEW.quantityInStock, CURDATE());
    END IF;
END;

CREATE TRIGGER order_details_change
AFTER INSERT ON orderdetails
FOR EACH ROW
BEGIN
    INSERT INTO order_details_history (orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber, effective_date) 
    VALUES (NEW.orderNumber, NEW.productCode, NEW.quantityOrdered, (SELECT buyPrice FROM products WHERE productCode = NEW.productCode), NEW.orderLineNumber, CURDATE());
END;

Now, every time a product's price or quantity in stock changes, a new row will be inserted into the product_history table. Similarly, when a new order detail is inserted, a record will be added to the order_details_history table with the product's price at the time of the order.

3. Fetching history:

To get the history of a product's price and inventory levels, you can use:

SELECT * 
FROM product_history 
WHERE productCode = 'S10_1678'
ORDER BY effective_date;

To get the history of orders and the product's price at the time of each sale:

SELECT * 
FROM order_details_history 
WHERE productCode = 'S10_1678'
ORDER BY effective_date;

This scenario illustrates a real-world use case where maintaining the history of changes is crucial for accurate reporting and analysis, particularly for financial audits and inventory management. Implementing such versioning tasks can become highly complex in large-scale data engineering workflows, especially when dealing with multiple related entities that need to be versioned and queried together.

These techniques also become extremely valuable when analyzing trends, making predictions, or performing retroactive analyses based on historical data.

Scenario 4: Analyzing Product Price Trends Over Time

In this scenario, imagine that the management at ClassicModels wants to analyze product price trends over time. They want to know which products have increased in price the most and which have decreased. This analysis could help them understand market trends, product performance, and help inform future pricing strategies.

Without the versioning implemented in the previous scenario, they would only be able to analyze the current product prices. Any historical price data would have been overwritten by updates, preventing this type of historical trend analysis. However, thanks to the versioning, we can perform this analysis with a query like:

SELECT 
    productCode, 
    MIN(buyPrice) as lowestPrice, 
    MAX(buyPrice) as highestPrice, 
    (MAX(buyPrice) - MIN(buyPrice)) as priceChange
FROM 
    product_history
GROUP BY 
    productCode
ORDER BY 
    priceChange DESC;

This will give us a list of products, sorted by the change in price over time. This could reveal valuable insights. For example, if a product has experienced a significant price increase but sales have remained stable, it could indicate that the product is in high demand and the company could potentially increase the price further. Conversely, a product with a significant price decrease might be underperforming and need a new marketing strategy.

Scenario 5: Inventory Management and Reordering Strategy

Inventory management is another crucial area where versioning can provide valuable insights. Suppose ClassicModels wants to optimize its reordering strategy. They want to minimize their storage costs by keeping their inventory as low as possible without running the risk of running out of stock.

Here, they could analyze the product_history table to understand how quickly each product's inventory decreases over time. For example, they might use a query like this:

SELECT 
    productCode, 
    AVG(quantityInStock) as avgStock, 
    MIN(quantityInStock) as minStock, 
    MAX(quantityInStock) as maxStock
FROM 
    product_history
GROUP BY 
    productCode;

This query would provide an overview of the average, minimum, and maximum inventory levels for each product. With this information, the company could identify products that frequently run low on stock and adjust their reordering strategy accordingly.

Scenario 6: Accurate Revenue Reporting

Imagine an audit scenario where ClassicModels needs to provide an accurate report of its revenues for a specific period in the past. Without versioning, if product prices have changed since then, their report would inaccurately calculate revenues based on the current prices.

Thanks to the order_details_history table, they can calculate the revenues accurately based on the prices at the time of each sale. For example, to calculate the revenues for Q1 2023, they could use a query like:

SELECT 
    SUM(priceEach * quantityOrdered) as revenue
FROM 
    order_details_history
WHERE 
    YEAR(effective_date) = 2023 AND QUARTER(effective_date) = 1;

Data versioning, therefore, can be extremely beneficial for businesses, from providing valuable insights for strategic decisions to ensuring accurate financial reporting. It's an advanced technique that requires careful planning and execution but can provide a significant return on investment in data-rich environments.

Scenario 7: Customer Behavior Analysis

The sales and marketing department of ClassicModels may want to understand customer purchasing behaviors over time, including any shifts in preferences or spending habits. Without data versioning, it would be challenging to gather accurate insights on this as customer order information would constantly be updated.

With data versioning, however, analysts can track these trends over time, allowing them to provide useful advice to the marketing team regarding when and how to target customers for maximum impact.

For instance, they may want to know which customers have significantly increased or decreased their spending over time. This could be accomplished with the following SQL statement:

SELECT 
    customerNumber, 
    MAX(totalValue) - MIN(totalValue) AS spendingDifference
FROM 
    (SELECT 
        customerNumber, 
        orderDate, 
        SUM(priceEach * quantityOrdered) AS totalValue 
     FROM 
        order_history 
     GROUP BY 
        customerNumber, orderDate) AS subquery
GROUP BY 
    customerNumber
ORDER BY 
    spendingDifference DESC;

This query would help identify customers who have had the most significant changes in their spending habits, allowing the marketing team to adjust their strategies accordingly.

Scenario 8: Supplier Price Fluctuations

Suppose the purchasing department of ClassicModels wants to investigate the fluctuations in prices from their suppliers over time. They aim to identify patterns in price increases, which would help in negotiating contracts and budgeting.

The product_history table would be immensely helpful here. The purchasing department could use a query like this:

SELECT 
    productCode, 
    supplier, 
    MAX(buyPrice) - MIN(buyPrice) AS priceFluctuation
FROM 
    product_history
GROUP BY 
    productCode, supplier
ORDER BY 
    priceFluctuation DESC;

This query would reveal the products and suppliers with the most significant price fluctuations, providing valuable insights to the purchasing department.

Scenario 9: Performance Metrics Over Time

The HR department might be interested in evaluating employee performance over time. In a sales-oriented organization like ClassicModels, a valuable performance metric could be the sales revenue brought in by each employee.

However, if an employee leaves the company or switches roles, their employeeNumber might be reassigned, and their sales record might be changed. This would make it impossible to accurately track the performance of employees over time.

With a versioned orders table, however, the HR department could analyze the performance of employees accurately and fairly. Here's a sample query:

SELECT 
    employeeNumber, 
    YEAR(effective_date) as year, 
    SUM(totalOrderValue) as annualSales 
FROM 
    (SELECT 
        employeeNumber, 
        effective_date, 
        SUM(priceEach * quantityOrdered) as totalOrderValue 
     FROM 
        orders_history 
     GROUP BY 
        employeeNumber, effective_date) AS subquery 
GROUP BY 
    employeeNumber, YEAR(effective_date) 
ORDER BY 
    annualSales DESC;

This query would provide a breakdown of annual sales for each employee, allowing the HR department to track performance over time accurately.

These scenarios demonstrate the power of data versioning in providing valuable, time-bound insights to all sectors of a business. By retaining historical data in a structured and accessible manner, companies can enhance their decision-making and strategy-planning processes.

Scenario 10: Forecasting Sales and Inventory Management

The ability to forecast future sales based on historical data is incredibly valuable. The supply chain management team at ClassicModels, for instance, would greatly benefit from accurate sales forecasts to manage their inventory more effectively.

Without versioned data, forecasting efforts may be hampered by changes in the data set that skew the underlying patterns and trends. With a versioned order_history table, the team could analyze the patterns in product demand over time and adjust their inventory levels accordingly.

SELECT 
    productCode, 
    YEAR(orderDate) as Year, 
    QUARTER(orderDate) as Quarter, 
    AVG(quantityOrdered) as Average_Quantity_Ordered
FROM 
    order_history
GROUP BY 
    productCode, YEAR(orderDate), QUARTER(orderDate)
ORDER BY 
    Year, Quarter;

This query calculates the average quantity ordered for each product on a quarterly basis, providing a historical perspective on demand trends that can inform inventory management decisions.

Scenario 11: Customer Retention Analysis

Customer retention is a crucial aspect of business growth. The marketing team at ClassicModels might want to understand the rate at which they are retaining customers over time.

With a versioned customer_history table, the marketing team could track changes in customer behavior, like if they stop placing orders. This would help the team to identify at-risk customers and target them with specific retention strategies.

SELECT 
    customerNumber,
    MIN(orderDate) as First_Order_Date, 
    MAX(orderDate) as Last_Order_Date, 
    DATEDIFF(MAX(orderDate),MIN(orderDate)) as Customer_Lifespan 
FROM 
    order_history
GROUP BY 
    customerNumber
HAVING 
    Customer_Lifespan > 0;

This query identifies customers who have placed orders over an extended period, providing a rough idea of customer lifespan.

Scenario 12: Tracking Price Changes

Understanding how product prices have changed over time can help the sales and marketing team devise effective pricing strategies and promotional offers. With a versioned products_history table, it would be possible to track these changes and trends.

SELECT 
    productCode,
    YEAR(effective_date) as Year, 
    AVG(buyPrice) as Average_Price 
FROM 
    product_history 
GROUP BY 
    productCode, YEAR(effective_date)
ORDER BY 
    Year, Average_Price;

This query provides a yearly average of the buy price for each product, allowing the team to see how prices have trended over time.

Through data versioning, ClassicModels can maintain the integrity of its historical data while still updating its operational data. This allows for accurate analysis of trends and patterns over time, supporting strategic decision-making across all departments.

Scenario 13: Effective Marketing Campaign

Imagine ClassicModels just finished a huge marketing campaign and now they want to analyze the effectiveness of the campaign. The marketing team wants to know whether there was a significant uptick in the number of new customers, total sales, and product sales in various regions during the campaign period.

Here, we can use CTEs, window functions, and joins to find this information.

WITH campaign_stats AS (
    SELECT 
        customers.customerNumber,
        customers.country,
        COUNT(DISTINCT orders.orderNumber) as num_orders,
        SUM(orderdetails.quantityOrdered * orderdetails.priceEach) as totalSales,
        COUNT(DISTINCT CASE WHEN customers.firstOrderDate BETWEEN '2024-01-01' AND '2024-12-31' THEN customers.customerNumber END) as new_customers,
        ROW_NUMBER() OVER(PARTITION BY customers.country ORDER BY SUM(orderdetails.quantityOrdered * orderdetails.priceEach) DESC) as rank
    FROM 
        customers
    JOIN 
        orders ON customers.customerNumber = orders.customerNumber
    JOIN 
        orderdetails ON orders.orderNumber = orderdetails.orderNumber
    WHERE 
        orders.orderDate BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY 
        customers.customerNumber, customers.country
)

SELECT * FROM campaign_stats
WHERE rank <= 5;

This query will first create a CTE that calculates the number of orders, total sales, and new customers during the campaign period in 2024. The data is partitioned by country and ranked by total sales. The main query then selects the top 5 customers from each country in terms of sales during the campaign period.

Scenario 14: Sales Forecasting

Suppose the company wants to forecast the next quarter sales based on the sales trend of the last four quarters. This will involve complex joins and window functions.

WITH sales_trend AS (
    SELECT 
        productCode,
        YEAR(orderDate) as year, 
        QUARTER(orderDate) as quarter,
        SUM(quantityOrdered * priceEach) as sales,
        AVG(SUM(quantityOrdered * priceEach)) OVER(PARTITION BY productCode ORDER BY YEAR(orderDate), QUARTER(orderDate) ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as moving_average
    FROM 
        orderdetails
    JOIN 
        orders ON orderdetails.orderNumber = orders.orderNumber
    GROUP BY 
        productCode, YEAR(orderDate), QUARTER(orderDate)
)

SELECT 
    productCode, 
    year, 
    quarter, 
    sales,
    moving_average, 
    (moving_average - sales)/moving_average * 100 as percentage_change
FROM 
    sales_trend
WHERE 
    year = YEAR(CURDATE()) AND quarter = QUARTER(CURDATE());

This query first calculates the total sales and moving average of sales for each product by quarter. The main query then calculates the percentage change in sales compared to the moving average for the current quarter.

Scenario 15: Customer Lifetime Value Calculation

ClassicModels would like to calculate the lifetime value of their customers, which is the total net profit they make from any given customer.

WITH customer_orders AS (
    SELECT 
        customers.customerNumber,
        SUM(orderdetails.quantityOrdered * orderdetails.priceEach) as totalSales,
        COUNT(DISTINCT orders.orderNumber) as totalOrders
    FROM 
        customers
    JOIN 
        orders ON customers.customerNumber = orders.customerNumber
    JOIN 
        orderdetails ON orders.orderNumber = orderdetails.orderNumber
    GROUP BY 
        customers.customerNumber
), 

average_order_value AS (
    SELECT 
        customerNumber,
        totalSales/totalOrders as avgOrderValue
    FROM 
        customer_orders
),

purchase_frequency AS (
    SELECT 
        customerNumber,
        totalOrders/(DATEDIFF(MAX(orders.orderDate), MIN(orders.orderDate))/365) as avgFrequency
    FROM 
        customer_orders
    JOIN 
        orders ON customer_orders.customerNumber = orders.customerNumber
    GROUP BY 
        customerNumber
)

SELECT 
    c.customerNumber, 
    c.customerName, 
    a.avgOrderValue * p.avgFrequency * t.margin * t.avgCustomerLifespan AS customerLifetimeValue
FROM 
    customers c
JOIN 
    average_order_value a ON c.customerNumber = a.customerNumber
JOIN 
    purchase_frequency p ON c.customerNumber = p.customerNumber
JOIN 
    (SELECT AVG(DATEDIFF(MAX(orders.orderDate), MIN(orders.orderDate))/365) as avgCustomerLifespan, AVG(orderdetails.priceEach - products.buyPrice) as margin 
    FROM orders 
    JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber 
    JOIN products ON orderdetails.productCode = products.productCode) t;

This query uses multiple CTEs to first calculate the average order value and average purchase frequency of each customer, then calculates the average customer lifespan and profit

margin. The main query then calculates the customer lifetime value by multiplying these values together. This complex calculation requires careful combination of multiple SQL techniques.