ETL_Orders_SQL.sql

-- =========================================
-- Create Dedicated SQL Pool Database
-- =========================================
CREATE DATABASE demo_db;  -- Run once
GO
USE demo_db;
GO

-- =========================================
-- External Data Source: Silver Layer in ADLS
-- =========================================
CREATE EXTERNAL DATA SOURCE ADLS_SILVER
WITH (
    LOCATION = 'abfss://silver@storageaccountncs.dfs.core.windows.net/'
);
GO

-- =========================================
-- External File Format for Delta Tables
-- =========================================
CREATE EXTERNAL FILE FORMAT delta_format
WITH ( FORMAT_TYPE = DELTA );
GO

-- =========================================
-- External Table: Orders from Silver Layer
-- =========================================
CREATE EXTERNAL TABLE ext_orders (
    order_id     VARCHAR(50),
    user_id      VARCHAR(50),
    product      VARCHAR(200),
    category     VARCHAR(200),
    price        FLOAT,
    quantity     INT,
    order_date   DATETIME2,
    status       VARCHAR(50),
    ingested_at  DATETIME2,
    order_ts     DATETIME2,
    dt           VARCHAR(50),
    total_value  BIGINT
)
WITH (
    LOCATION    = 'orders_delta/',
    DATA_SOURCE = ADLS_SILVER,
    FILE_FORMAT = delta_format
);
GO

-- =========================================
-- Query: Average Order Value (AOV) by Category
-- Only includes Delivered orders
-- =========================================
SELECT 
    category,
    CAST(AVG(CASE WHEN status = 'Delivered' THEN total_value END) AS DECIMAL(10,2)) AS avg_order_value
FROM ext_orders
GROUP BY category
ORDER BY avg_order_value DESC;
GO