sp_create_master_view.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_create_master_view $$
CREATE PROCEDURE sp_create_master_view()
BEGIN
  DROP VIEW IF EXISTS v_sales_master;

  CREATE VIEW v_sales_master AS
  SELECT
    s.order_id,
    s.order_date,
    s.order_number,
    s.order_quantity,
    s.year,
    s.month,
    s.quarter,
    s.day_of_week,
    s.is_weekend,

    -- Customer features
    c.customer_id,
    c.age,
    c.marital_status,
    c.gender,
    c.annual_income,
    c.income_category,
    c.total_children,
    c.education_level,
    c.occupation,
    c.homeowner,

    -- Product features
    p.product_id,
    p.product_name,
    p.product_color,
    p.product_size,
    p.product_cost,
    p.product_price,
    p.profit_margin,
    p.price_category,

    -- Territory features (from CSV columns)
    s.territory_id,
    t.Region    AS territory_name,
    t.Country   AS territory_country,
    t.Continent AS territory_group,

    -- Category features (from CSV columns)
    pc.ProductCategoryKey      AS category_id,
    pc.CategoryName            AS category_name,
    psc.ProductSubcategoryKey  AS subcategory_id,
    psc.SubcategoryName        AS subcategory_name,

    -- Calculated features
    (s.order_quantity * p.product_price)                           AS total_revenue,
    (s.order_quantity * p.product_cost)                            AS total_cost,
    (s.order_quantity * p.product_price) - (s.order_quantity * p.product_cost) AS total_profit
  FROM sales_clean s
  LEFT JOIN customers_clean c ON s.customer_id = c.customer_id
  LEFT JOIN products_clean p ON s.product_id = p.product_id
  LEFT JOIN territories t ON s.territory_id = CAST(t.SalesTerritoryKey AS UNSIGNED)
  LEFT JOIN product_subcategories psc ON p.product_subcategory_id = psc.ProductSubcategoryKey
  LEFT JOIN product_categories pc ON psc.ProductCategoryKey = pc.ProductCategoryKey;
END $$

DELIMITER ;