The last-mile knowledge repository for decentralized analytics teams.

-- ====================================================================
-- SAP SOURCE TO SEMANTIC ANALYTICS LAYER
-- Objective: Calculate Profitability (Gross Margin %) for Top-Selling Products
--            based on Sales Data (VBRP) and Product Master Data (MARA).
--
-- Note: This simulation uses common SAP table/field names (e.g., KUNNR, MATNR).
-- ====================================================================

-- --------------------------------------------------------------------
-- DDL (Data Definition Language) - SAP Source Table Simulation
-- --------------------------------------------------------------------

-- SAP Table KNA1: Customer Master (General Data)
CREATE TABLE KNA1 (
    KUNNR VARCHAR(10) PRIMARY KEY, -- Customer Number (Semantic: Customer_ID)
    NAME1 VARCHAR(100),            -- Customer Name (Semantic: Customer_Name)
    LAND1 VARCHAR(3)               -- Country Key (Semantic: Country_Code)
);

-- SAP Table MARA: General Material Data (Product Master)
CREATE TABLE MARA (
    MATNR VARCHAR(18) PRIMARY KEY, -- Material Number (Semantic: Product_SKU)
    MATKL VARCHAR(9),              -- Material Group (Semantic: Product_Category)
    BRGEW DECIMAL(13, 3)           -- Gross Weight (Semantic: Standard_Weight_KG)
);

-- SAP Table VBRP: Sales Document Item Data (Billing Document Line Items)
CREATE TABLE VBRP (
    VBELN VARCHAR(10) NOT NULL,    -- Billing Document (Semantic: Invoice_Number)
    POSNR VARCHAR(6) NOT NULL,     -- Billing Item (Semantic: Invoice_Line_Item)
    KUNNR VARCHAR(10),             -- Sold-to Party (Semantic: Customer_ID)
    MATNR VARCHAR(18),             -- Material Number (Semantic: Product_SKU)
    FKIMG DECIMAL(13, 3),          -- Billed Quantity (Semantic: Quantity_Shipped)
    NETWR DECIMAL(13, 2),          -- Net Value (Semantic: Net_Sales_Amount)
    KBETR DECIMAL(13, 2),          -- Price/Cost of Goods Sold (Internal Cost used here) (Semantic: COGS_Amount)
    FKDAT DATE,                    -- Billing Date (Semantic: Invoice_Date)
    PRIMARY KEY (VBELN, POSNR)
);


-- --------------------------------------------------------------------
-- DML (Data Manipulation Language) - Sample Data Population
-- --------------------------------------------------------------------

INSERT INTO KNA1 (KUNNR, NAME1, LAND1) VALUES
('C0001', 'Global Tech Corp', 'US'),
('C0002', 'Regional Retail Co', 'CA'),
('C0003', 'Small Business Inc', 'MX');

INSERT INTO MARA (MATNR, MATKL, BRGEW) VALUES
('P1001', 'ELECT', 1.500), -- Laptop
('P1002', 'SOFTW', 0.000), -- License
('P1003', 'ACCES', 0.250); -- Mouse

INSERT INTO VBRP (VBELN, POSNR, KUNNR, MATNR, FKIMG, NETWR, KBETR, FKDAT) VALUES
-- High-margin sale of P1002 to C0001
('I4001', '000010', 'C0001', 'P1002', 1.000, 5000.00, 50.00, '2024-10-25'),
-- Low-margin sale of P1001 to C0001
('I4002', '000010', 'C0001', 'P1001', 10.000, 12000.00, 10000.00, '2024-11-01'),
-- Standard sale of P1003 to C0002
('I4003', '000010', 'C0002', 'P1003', 50.000, 2500.00, 1250.00, '2024-11-05');


-- --------------------------------------------------------------------
-- DQL (Data Query Language) - Semantic Analysis
-- Objective: Calculate Gross Margin % by Product Category
-- --------------------------------------------------------------------

WITH RenamedSalesData AS (
    -- Step 1: Join and apply semantic naming conventions immediately
    SELECT
        b.FKDAT AS Invoice_Date,
        b.VBELN AS Invoice_Number,
        b.POSNR AS Invoice_Line_Item,
        c.NAME1 AS Customer_Name,
        c.KUNNR AS Customer_ID,
        c.LAND1 AS Country_Code,
        m.MATNR AS Product_SKU,
        m.MATKL AS Product_Category,
        m.BRGEW AS Standard_Weight_KG,
        b.FKIMG AS Quantity_Shipped,
        b.NETWR AS Net_Sales_Amount,
        b.KBETR AS COGS_Amount, -- Cost of Goods Sold
        (b.NETWR - b.KBETR) AS Gross_Profit_Amount -- Calculated metric
    FROM VBRP b
    JOIN KNA1 c ON b.KUNNR = c.KUNNR -- Link to Customer Master
    JOIN MARA m ON b.MATNR = m.MATNR -- Link to Product Master
    WHERE
        b.FKDAT >= DATE '2024-10-01' -- Filter by recent activity
),
CategoryProfitability AS (
    -- Step 2: Aggregate by the new semantic Product_Category alias
    SELECT
        Product_Category,
        COUNT(DISTINCT Invoice_Number) AS Total_Invoices,
        SUM(Quantity_Shipped) AS Total_Units_Sold,
        SUM(Net_Sales_Amount) AS Total_Net_Revenue,
        SUM(Gross_Profit_Amount) AS Total_Gross_Profit,
        -- Calculate the key performance indicator (KPI): Gross Margin Percentage
        ROUND(
            (SUM(Gross_Profit_Amount) * 100.0) / NULLIF(SUM(Net_Sales_Amount), 0),
            2
        ) AS Gross_Margin_Percent
    FROM RenamedSalesData
    GROUP BY
        Product_Category
)
-- Final Select: Present the strategic KPI results using only semantic aliases
SELECT
    cp.Product_Category,
    cp.Total_Invoices,
    cp.Total_Units_Sold,
    cp.Total_Net_Revenue,
    cp.Total_Gross_Profit,
    cp.Gross_Margin_Percent,
    (
        -- Example of a final lookup/scalar subquery using the semantic layer
        SELECT AVG(Standard_Weight_KG)
        FROM RenamedSalesData rsd
        WHERE rsd.Product_Category = cp.Product_Category
    ) AS Avg_Shipment_Weight_Category
FROM CategoryProfitability cp
WHERE cp.Total_Net_Revenue > 1000 -- Focus on material categories
ORDER BY
    cp.Gross_Margin_Percent DESC;