OLAP and Data Warehousing - Analytical Processing

CREATE TABLE fact_sales (
    sale_id         BIGINT PRIMARY KEY,
    date_key        INTEGER REFERENCES dim_date(date_key),
    product_key     INTEGER REFERENCES dim_product(product_key),
    store_key       INTEGER REFERENCES dim_store(store_key),
    customer_key    INTEGER REFERENCES dim_customer(customer_key),
    
    -- Measures (facts)
    quantity        INTEGER,
    unit_price      DECIMAL(10, 2),
    discount_amount DECIMAL(10, 2),
    total_amount    DECIMAL(12, 2),
    cost_amount     DECIMAL(10, 2),
    profit_amount   DECIMAL(10, 2)
);

CREATE TABLE dim_product (
    product_key     INTEGER PRIMARY KEY,
    product_id      VARCHAR(20),
    product_name    VARCHAR(200),
    category        VARCHAR(100),
    subcategory     VARCHAR(100),
    brand           VARCHAR(100),
    supplier        VARCHAR(100),
    unit_cost       DECIMAL(10, 2),
    
    -- SCD Type 2 columns
    effective_date  DATE,
    expiration_date DATE,
    is_current      BOOLEAN
);

CREATE TABLE dim_date (
    date_key        INTEGER PRIMARY KEY,
    date_value      DATE UNIQUE,
    day_of_week     VARCHAR(10),
    day_of_month    INTEGER,
    day_of_year     INTEGER,
    week_of_year    INTEGER,
    month_number    INTEGER,
    month_name      VARCHAR(10),
    quarter         INTEGER,
    year            INTEGER,
    is_weekend      BOOLEAN,
    is_holiday      BOOLEAN,
    fiscal_quarter  INTEGER,
    fiscal_year     INTEGER
);

-- Create dimension tables
CREATE TABLE dim_date (
    date_key INTEGER PRIMARY KEY,
    date_value DATE NOT NULL,
    day_name VARCHAR(10),
    day_of_week INTEGER,
    day_of_month INTEGER,
    week_of_year INTEGER,
    month_number INTEGER,
    month_name VARCHAR(15),
    quarter INTEGER,
    year INTEGER,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN
);

CREATE TABLE dim_product (
    product_key INTEGER PRIMARY KEY,
    product_id VARCHAR(20) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    brand VARCHAR(100),
    unit_cost DECIMAL(10, 2),
    -- SCD Type 2
    valid_from DATE NOT NULL,
    valid_to DATE,
    is_current BOOLEAN DEFAULT TRUE
);

CREATE TABLE dim_customer (
    customer_key INTEGER PRIMARY KEY,
    customer_id VARCHAR(20) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    city VARCHAR(100),
    state VARCHAR(50),
    country VARCHAR(50),
    customer_segment VARCHAR(20),
    -- SCD Type 2
    valid_from DATE NOT NULL,
    valid_to DATE,
    is_current BOOLEAN DEFAULT TRUE
);

CREATE TABLE dim_store (
    store_key INTEGER PRIMARY KEY,
    store_id VARCHAR(20) NOT NULL,
    store_name VARCHAR(100),
    city VARCHAR(100),
    state VARCHAR(50),
    country VARCHAR(50),
    region VARCHAR(50),
    store_type VARCHAR(50)
);

-- Create fact table
CREATE TABLE fact_sales (
    sale_key BIGINT PRIMARY KEY,
    date_key INTEGER REFERENCES dim_date(date_key),
    product_key INTEGER REFERENCES dim_product(product_key),
    customer_key INTEGER REFERENCES dim_customer(customer_key),
    store_key INTEGER REFERENCES dim_store(store_key),
    
    -- Degenerate dimensions (from transaction)
    order_number VARCHAR(20),
    line_number INTEGER,
    
    -- Facts (measures)
    quantity INTEGER,
    unit_price DECIMAL(10, 2),
    discount_percent DECIMAL(5, 2),
    discount_amount DECIMAL(10, 2),
    tax_amount DECIMAL(10, 2),
    total_amount DECIMAL(12, 2),
    cost_amount DECIMAL(10, 2),
    profit_amount DECIMAL(12, 2)
);

-- Populate date dimension (useful utility)
INSERT INTO dim_date
SELECT
    TO_CHAR(d, 'YYYYMMDD')::INTEGER AS date_key,
    d AS date_value,
    TO_CHAR(d, 'Day') AS day_name,
    EXTRACT(ISODOW FROM d) AS day_of_week,
    EXTRACT(DAY FROM d) AS day_of_month,
    EXTRACT(WEEK FROM d) AS week_of_year,
    EXTRACT(MONTH FROM d) AS month_number,
    TO_CHAR(d, 'Month') AS month_name,
    EXTRACT(QUARTER FROM d) AS quarter,
    EXTRACT(YEAR FROM d) AS year,
    EXTRACT(ISODOW FROM d) IN (6, 7) AS is_weekend,
    FALSE AS is_holiday -- Update with actual holidays
FROM GENERATE_SERIES('2020-01-01'::DATE, '2030-12-31'::DATE, '1 day') AS d;

-- Basic dimensional query: Sales by product category and month
SELECT 
    dd.year,
    dd.month_name,
    dp.category,
    SUM(fs.quantity) AS total_units,
    SUM(fs.total_amount) AS total_revenue,
    SUM(fs.profit_amount) AS total_profit
FROM fact_sales fs
JOIN dim_date dd ON fs.date_key = dd.date_key
JOIN dim_product dp ON fs.product_key = dp.product_key
WHERE dd.year = 2024
GROUP BY dd.year, dd.month_number, dd.month_name, dp.category
ORDER BY dd.month_number, total_revenue DESC;

-- Year-over-Year comparison
WITH monthly_sales AS (
    SELECT
        dd.year,
        dd.month_number,
        SUM(fs.total_amount) AS revenue
    FROM fact_sales fs
    JOIN dim_date dd ON fs.date_key = dd.date_key
    GROUP BY dd.year, dd.month_number
)
SELECT
    curr.year,
    curr.month_number,
    curr.revenue AS current_revenue,
    prev.revenue AS previous_year_revenue,
    curr.revenue - prev.revenue AS yoy_change,
    ROUND((curr.revenue - prev.revenue) / prev.revenue * 100, 2) AS yoy_percent
FROM monthly_sales curr
LEFT JOIN monthly_sales prev 
    ON curr.month_number = prev.month_number 
    AND curr.year = prev.year + 1
WHERE curr.year = 2024
ORDER BY curr.month_number;

-- Running total and ranking
SELECT
    dd.year,
    dd.month_number,
    dp.category,
    SUM(fs.total_amount) AS monthly_revenue,
    SUM(SUM(fs.total_amount)) OVER (
        PARTITION BY dp.category
        ORDER BY dd.year, dd.month_number
    ) AS running_total,
    RANK() OVER (
        PARTITION BY dd.year, dd.month_number
        ORDER BY SUM(fs.total_amount) DESC
    ) AS category_rank
FROM fact_sales fs
JOIN dim_date dd ON fs.date_key = dd.date_key
JOIN dim_product dp ON fs.product_key = dp.product_key
WHERE dd.year >= 2023
GROUP BY dd.year, dd.month_number, dp.category
ORDER BY dd.year, dd.month_number, category_rank;

-- Customer cohort analysis
WITH first_purchase AS (
    SELECT 
        customer_key,
        MIN(date_key) AS first_purchase_date_key
    FROM fact_sales
    GROUP BY customer_key
),
cohort_revenue AS (
    SELECT
        dd_first.year AS cohort_year,
        dd_first.month_number AS cohort_month,
        EXTRACT(YEAR FROM dd.date_value) * 12 + dd.month_number -
        (dd_first.year * 12 + dd_first.month_number) AS months_since_first,
        SUM(fs.total_amount) AS revenue,
        COUNT(DISTINCT fs.customer_key) AS customers
    FROM fact_sales fs
    JOIN first_purchase fp ON fs.customer_key = fp.customer_key
    JOIN dim_date dd ON fs.date_key = dd.date_key
    JOIN dim_date dd_first ON fp.first_purchase_date_key = dd_first.date_key
    GROUP BY dd_first.year, dd_first.month_number, months_since_first
)
SELECT
    cohort_year,
    cohort_month,
    months_since_first,
    customers,
    revenue,
    ROUND(revenue / NULLIF(customers, 0), 2) AS revenue_per_customer
FROM cohort_revenue
WHERE cohort_year = 2024
ORDER BY cohort_year, cohort_month, months_since_first;

-- BigQuery: Partitioning and clustering
CREATE TABLE `project.dataset.fact_sales`
PARTITION BY DATE(sale_date)
CLUSTER BY store_key, product_key
AS
SELECT * FROM source_sales;

-- Snowflake: Time travel (query data as it existed in the past)
SELECT * FROM fact_sales
AT(TIMESTAMP => '2024-01-01 00:00:00'::TIMESTAMP);

-- Snowflake: Clone for testing
CREATE TABLE fact_sales_test CLONE fact_sales;

-- BigQuery: Nested and repeated fields
SELECT
    order_id,
    customer.name AS customer_name,
    customer.email AS customer_email,
    ARRAY_LENGTH(items) AS item_count,
    (SELECT SUM(item.quantity * item.price) FROM UNNEST(items) AS item) AS total
FROM orders;

-- Window functions for advanced analytics
SELECT
    store_key,
    date_key,
    daily_revenue,
    -- Moving average
    AVG(daily_revenue) OVER (
        PARTITION BY store_key
        ORDER BY date_key
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS revenue_7day_avg,
    -- Percent of total
    daily_revenue / SUM(daily_revenue) OVER (
        PARTITION BY date_key
    ) * 100 AS percent_of_daily_total,
    -- Comparison to previous period
    daily_revenue - LAG(daily_revenue) OVER (
        PARTITION BY store_key
        ORDER BY date_key
    ) AS change_from_previous_day
FROM (
    SELECT 
        store_key,
        date_key,
        SUM(total_amount) AS daily_revenue
    FROM fact_sales
    GROUP BY store_key, date_key
) daily_sales;

-- ELT Pattern: Load raw data, then transform in warehouse

-- Step 1: Create staging table for raw data
CREATE TABLE staging_orders (
    raw_data JSON,
    load_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    source_file VARCHAR(255)
);

-- Step 2: Transform into dimensional model
INSERT INTO dim_customer (customer_key, customer_id, first_name, last_name, email, valid_from, is_current)
SELECT
    ROW_NUMBER() OVER (ORDER BY customer_id) AS customer_key,
    customer_id,
    first_name,
    last_name,
    email,
    CURRENT_DATE AS valid_from,
    TRUE AS is_current
FROM (
    SELECT DISTINCT
        raw_data->>'customer_id' AS customer_id,
        raw_data->'customer'->>'first_name' AS first_name,
        raw_data->'customer'->>'last_name' AS last_name,
        raw_data->'customer'->>'email' AS email
    FROM staging_orders
    WHERE raw_data->>'customer_id' NOT IN (SELECT customer_id FROM dim_customer WHERE is_current = TRUE)
);

-- Step 3: Load fact table
INSERT INTO fact_sales
SELECT
    (raw_data->>'order_id')::BIGINT AS sale_key,
    TO_CHAR((raw_data->>'order_date')::DATE, 'YYYYMMDD')::INTEGER AS date_key,
    dp.product_key,
    dc.customer_key,
    ds.store_key,
    raw_data->>'order_number' AS order_number,
    1 AS line_number,
    (raw_data->>'quantity')::INTEGER AS quantity,
    (raw_data->>'unit_price')::DECIMAL(10,2) AS unit_price,
    0 AS discount_percent,
    0 AS discount_amount,
    (raw_data->>'tax')::DECIMAL(10,2) AS tax_amount,
    (raw_data->>'total')::DECIMAL(12,2) AS total_amount,
    (raw_data->>'cost')::DECIMAL(10,2) AS cost_amount,
    (raw_data->>'total')::DECIMAL(12,2) - (raw_data->>'cost')::DECIMAL(10,2) AS profit_amount
FROM staging_orders so
JOIN dim_product dp ON so.raw_data->>'product_id' = dp.product_id AND dp.is_current = TRUE
JOIN dim_customer dc ON so.raw_data->>'customer_id' = dc.customer_id AND dc.is_current = TRUE
JOIN dim_store ds ON so.raw_data->>'store_id' = ds.store_id;

-- Fact table for orders
CREATE TABLE fact_orders (
    order_key BIGINT PRIMARY KEY,
    date_key INTEGER,
    product_key INTEGER,
    customer_key INTEGER,
    promotion_key INTEGER,
    shipping_address_key INTEGER,
    
    -- Measures
    quantity INTEGER,
    unit_price DECIMAL(10, 2),
    discount_amount DECIMAL(10, 2),
    shipping_cost DECIMAL(10, 2),
    tax_amount DECIMAL(10, 2),
    total_amount DECIMAL(12, 2)
);

-- Fact table for returns (separate grain)
CREATE TABLE fact_returns (
    return_key BIGINT PRIMARY KEY,
    original_order_key BIGINT,
    return_date_key INTEGER,
    product_key INTEGER,
    customer_key INTEGER,
    return_reason_key INTEGER,
    
    -- Measures
    quantity_returned INTEGER,
    refund_amount DECIMAL(10, 2)
);

-- Dimension tables
CREATE TABLE dim_promotion (
    promotion_key INTEGER PRIMARY KEY,
    promotion_id VARCHAR(20),
    promotion_name VARCHAR(100),
    promotion_type VARCHAR(50),  -- Discount, BOGO, Free Shipping
    discount_percent DECIMAL(5, 2),
    start_date DATE,
    end_date DATE,
    marketing_channel VARCHAR(50)
);

CREATE TABLE dim_geography (
    geography_key INTEGER PRIMARY KEY,
    city VARCHAR(100),
    state VARCHAR(50),
    country VARCHAR(50),
    region VARCHAR(50),
    postal_code VARCHAR(20),
    latitude DECIMAL(10, 6),
    longitude DECIMAL(10, 6)
);

CREATE TABLE dim_return_reason (
    return_reason_key INTEGER PRIMARY KEY,
    reason_code VARCHAR(20),
    reason_description VARCHAR(200),
    reason_category VARCHAR(50)  -- Quality, Wrong Item, Changed Mind
);

-- Sample analytical queries

-- Promotion effectiveness analysis
SELECT
    dp.promotion_name,
    dp.promotion_type,
    COUNT(DISTINCT fo.order_key) AS order_count,
    SUM(fo.total_amount) AS total_revenue,
    SUM(fo.discount_amount) AS total_discounts,
    AVG(fo.total_amount) AS avg_order_value,
    COUNT(DISTINCT fr.return_key) AS return_count,
    SUM(fr.refund_amount) AS total_refunds,
    SUM(fo.total_amount) - SUM(COALESCE(fr.refund_amount, 0)) AS net_revenue
FROM fact_orders fo
JOIN dim_promotion dp ON fo.promotion_key = dp.promotion_key
LEFT JOIN fact_returns fr ON fo.order_key = fr.original_order_key
GROUP BY dp.promotion_key, dp.promotion_name, dp.promotion_type
ORDER BY net_revenue DESC;

-- Return rate by product category
SELECT
    dprod.category,
    COUNT(fo.order_key) AS orders,
    SUM(fo.quantity) AS units_sold,
    COUNT(fr.return_key) AS returns,
    SUM(COALESCE(fr.quantity_returned, 0)) AS units_returned,
    ROUND(SUM(COALESCE(fr.quantity_returned, 0))::DECIMAL / NULLIF(SUM(fo.quantity), 0) * 100, 2) AS return_rate
FROM fact_orders fo
JOIN dim_product dprod ON fo.product_key = dprod.product_key
LEFT JOIN fact_returns fr ON fo.order_key = fr.original_order_key AND fo.product_key = fr.product_key
GROUP BY dprod.category
ORDER BY return_rate DESC;

-- Customer Lifetime Value
WITH customer_metrics AS (
    SELECT
        dc.customer_key,
        dc.first_name || ' ' || dc.last_name AS customer_name,
        dc.customer_segment,
        MIN(dd.date_value) AS first_purchase,
        MAX(dd.date_value) AS last_purchase,
        COUNT(DISTINCT fo.order_key) AS total_orders,
        SUM(fo.total_amount) AS lifetime_value,
        AVG(fo.total_amount) AS avg_order_value
    FROM fact_orders fo
    JOIN dim_customer dc ON fo.customer_key = dc.customer_key
    JOIN dim_date dd ON fo.date_key = dd.date_key
    GROUP BY dc.customer_key, dc.first_name, dc.last_name, dc.customer_segment
)
SELECT
    customer_segment,
    COUNT(*) AS customer_count,
    AVG(lifetime_value) AS avg_clv,
    AVG(total_orders) AS avg_orders,
    AVG(avg_order_value) AS avg_aov,
    AVG(EXTRACT(DAY FROM last_purchase - first_purchase)) AS avg_customer_lifespan_days
FROM customer_metrics
GROUP BY customer_segment
ORDER BY avg_clv DESC;

-- RFM Analysis (Recency, Frequency, Monetary)
WITH rfm_base AS (
    SELECT
        dc.customer_key,
        dc.first_name || ' ' || dc.last_name AS customer_name,
        MAX(dd.date_value) AS last_purchase_date,
        COUNT(DISTINCT fo.order_key) AS frequency,
        SUM(fo.total_amount) AS monetary
    FROM fact_orders fo
    JOIN dim_customer dc ON fo.customer_key = dc.customer_key
    JOIN dim_date dd ON fo.date_key = dd.date_key
    WHERE dd.date_value >= CURRENT_DATE - INTERVAL '2 years'
    GROUP BY dc.customer_key, dc.first_name, dc.last_name
),
rfm_scores AS (
    SELECT
        customer_key,
        customer_name,
        CURRENT_DATE - last_purchase_date AS recency_days,
        frequency,
        monetary,
        NTILE(5) OVER (ORDER BY last_purchase_date DESC) AS r_score,
        NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
        NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
    FROM rfm_base
)
SELECT
    customer_key,
    customer_name,
    recency_days,
    frequency,
    monetary,
    r_score,
    f_score,
    m_score,
    (r_score + f_score + m_score) AS rfm_total,
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 4 AND f_score >= 3 THEN 'Loyal Customers'
        WHEN r_score >= 3 AND m_score >= 4 THEN 'Potential Loyalists'
        WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
        WHEN r_score <= 2 AND f_score >= 4 THEN 'At Risk'
        WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
        ELSE 'Others'
    END AS customer_segment
FROM rfm_scores
ORDER BY rfm_total DESC;

-- Purchase frequency distribution
SELECT
    frequency_bucket,
    COUNT(*) AS customer_count,
    SUM(total_revenue) AS total_revenue,
    AVG(total_revenue) AS avg_revenue
FROM (
    SELECT
        customer_key,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_revenue,
        CASE
            WHEN COUNT(*) = 1 THEN '1 order'
            WHEN COUNT(*) BETWEEN 2 AND 3 THEN '2-3 orders'
            WHEN COUNT(*) BETWEEN 4 AND 6 THEN '4-6 orders'
            WHEN COUNT(*) BETWEEN 7 AND 10 THEN '7-10 orders'
            ELSE '10+ orders'
        END AS frequency_bucket
    FROM fact_orders
    GROUP BY customer_key
) customer_orders
GROUP BY frequency_bucket
ORDER BY MIN(order_count);