complex_queries.sql

/*
 * ============================================================
 *  COMPLEX SQL QUERIES — Advanced Analytics
 * ============================================================
 *  This file demonstrates advanced SQL techniques:
 *  - Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE)
 *  - Common Table Expressions (CTEs)
 *  - Running Totals & Moving Averages
 *  - Self-JOINs
 *  - PIVOT / UNPIVOT
 *  - Cumulative Distribution
 *  - Gap Analysis
 * ============================================================
 */

USE sales_analysis_db;
GO


- ============================================================
- 1. WINDOW FUNCTIONS: Ranking & Comparison
- ============================================================

- 1a. ROW_NUMBER: Unique rank for each product by sales (no ties)
SELECT
    p.product_name,
    p.category,
    SUM(o.sales) AS total_sales,
    ROW_NUMBER() OVER (ORDER BY SUM(o.sales) DESC) AS row_num,
    RANK() OVER (ORDER BY SUM(o.sales) DESC) AS rank_with_ties,
    DENSE_RANK() OVER (ORDER BY SUM(o.sales) DESC) AS dense_rank
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY p.product_name, p.category;
GO


- 1b. RANK within each category (partition by category)
SELECT
    p.category,
    p.product_name,
    SUM(o.sales) AS total_sales,
    RANK() OVER (
        PARTITION BY p.category
        ORDER BY SUM(o.sales) DESC
    ) AS category_rank
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY p.category, p.product_name;
GO


- 1c. NTILE: Divide customers into quartiles by spending
SELECT
    c.customer_name,
    SUM(o.sales) AS total_spend,
    NTILE(4) OVER (ORDER BY SUM(o.sales) DESC) AS spend_quartile
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
GROUP BY c.customer_name;
GO


- ============================================================
- 2. LAG / LEAD: Month-over-Month Comparison
- ============================================================

- 2a. Monthly sales with previous month comparison and growth rate
WITH monthly_sales AS (
    SELECT
        YEAR(order_date) AS year,
        MONTH(order_date) AS month,
        SUM(sales) AS monthly_sales,
        SUM(profit) AS monthly_profit
    FROM Orders
    GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT
    year,
    month,
    monthly_sales,
    LAG(monthly_sales, 1) OVER (ORDER BY year, month) AS prev_month_sales,
    LEAD(monthly_sales, 1) OVER (ORDER BY year, month) AS next_month_sales,
    ROUND(
        (monthly_sales - LAG(monthly_sales, 1) OVER (ORDER BY year, month))
        / NULLIF(LAG(monthly_sales, 1) OVER (ORDER BY year, month), 0) * 100,
        2
    ) AS mom_growth_pct
FROM monthly_sales
ORDER BY year, month;
GO


- ============================================================
- 3. RUNNING TOTALS & MOVING AVERAGES
- ============================================================

- 3a. Cumulative (running) total of sales over time
WITH daily_sales AS (
    SELECT
        order_date,
        SUM(sales) AS day_sales
    FROM Orders
    GROUP BY order_date
)
SELECT
    order_date,
    day_sales,
    SUM(day_sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(day_sales) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3day,
    SUM(day_sales) OVER () AS grand_total,
    ROUND(
        SUM(day_sales) OVER (
            ORDER BY order_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) * 100.0 / SUM(day_sales) OVER (),
        2
    ) AS cumulative_pct
FROM daily_sales
ORDER BY order_date;
GO


- 3b. Running total by category (partitioned)
WITH category_monthly AS (
    SELECT
        p.category,
        YEAR(o.order_date) AS year,
        MONTH(o.order_date) AS month,
        SUM(o.sales) AS monthly_sales
    FROM Orders o
    JOIN Products p ON o.product_id = p.product_id
    GROUP BY p.category, YEAR(o.order_date), MONTH(o.order_date)
)
SELECT
    category,
    year,
    month,
    monthly_sales,
    SUM(monthly_sales) OVER (
        PARTITION BY category
        ORDER BY year, month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS category_running_total
FROM category_monthly
ORDER BY category, year, month;
GO


- ============================================================
- 4. CTEs: Multi-Step Analysis
- ============================================================

- 4a. Customer segmentation using CTE chain
- Step 1: Calculate customer metrics
- Step 2: Assign segments based on thresholds
- Step 3: Summarize segments

WITH customer_metrics AS (
    SELECT
        c.customer_id,
        c.customer_name,
        c.segment,
        COUNT(DISTINCT o.order_id) AS order_count,
        SUM(o.sales) AS total_sales,
        SUM(o.profit) AS total_profit,
        AVG(o.sales) AS avg_order_value,
        DATEDIFF(DAY, MIN(o.order_date), MAX(o.order_date)) AS customer_lifespan_days
    FROM Customers c
    JOIN Orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name, c.segment
),
customer_tiers AS (
    SELECT
        *,
        CASE
            WHEN total_sales >= 40000 THEN 'High Value'
            WHEN total_sales >= 10000 THEN 'Medium Value'
            ELSE 'Low Value'
        END AS value_tier,
        CASE
            WHEN order_count >= 3 THEN 'Frequent'
            WHEN order_count >= 2 THEN 'Occasional'
            ELSE 'One-Time'
        END AS frequency_tier
    FROM customer_metrics
)
SELECT
    value_tier,
    frequency_tier,
    COUNT(*) AS customer_count,
    ROUND(AVG(total_sales), 2) AS avg_sales,
    ROUND(AVG(total_profit), 2) AS avg_profit,
    SUM(total_sales) AS segment_total_sales
FROM customer_tiers
GROUP BY value_tier, frequency_tier
ORDER BY segment_total_sales DESC;
GO


- ============================================================
- 5. SELF-JOIN: Customer Repeat Purchases
- ============================================================

- 5a. Find customers who bought products from multiple categories
SELECT DISTINCT
    c.customer_name,
    o1_cat.category AS category_1,
    o2_cat.category AS category_2
FROM Orders o1
JOIN Orders o2
    ON o1.customer_id = o2.customer_id
    AND o1.product_id <> o2.product_id
JOIN Customers c ON o1.customer_id = c.customer_id
JOIN Products o1_cat ON o1.product_id = o1_cat.product_id
JOIN Products o2_cat ON o2.product_id = o2_cat.product_id
WHERE o1_cat.category <> o2_cat.category;
GO


- 5b. Self-JOIN: Orders placed by the same customer on different dates
SELECT
    c.customer_name,
    o1.order_id AS order_1,
    o1.order_date AS date_1,
    o2.order_id AS order_2,
    o2.order_date AS date_2,
    DATEDIFF(DAY, o1.order_date, o2.order_date) AS days_between
FROM Orders o1
JOIN Orders o2
    ON o1.customer_id = o2.customer_id
    AND o1.order_date < o2.order_date
JOIN Customers c ON o1.customer_id = c.customer_id
ORDER BY c.customer_name, o1.order_date;
GO


- ============================================================
- 6. PIVOT: Sales by Category across Months
- ============================================================

- 6a. Pivot table: Categories as columns, months as rows
SELECT *
FROM (
    SELECT
        MONTH(o.order_date) AS order_month,
        p.category,
        o.sales
    FROM Orders o
    JOIN Products p ON o.product_id = p.product_id
) AS source_data
PIVOT (
    SUM(sales)
    FOR category IN ([Technology], [Furniture], [Office Supplies])
) AS pivot_table
ORDER BY order_month;
GO


- 6b. UNPIVOT: Convert pivoted data back to rows (useful for reporting)
WITH pivoted AS (
    SELECT *
    FROM (
        SELECT
            MONTH(o.order_date) AS order_month,
            p.category,
            o.sales
        FROM Orders o
        JOIN Products p ON o.product_id = p.product_id
    ) AS src
    PIVOT (
        SUM(sales)
        FOR category IN ([Technology], [Furniture], [Office Supplies])
    ) AS pvt
)
SELECT
    order_month,
    category,
    total_sales
FROM pivoted
UNPIVOT (
    total_sales FOR category IN ([Technology], [Furniture], [Office Supplies])
) AS unpvt
ORDER BY order_month, category;
GO


- ============================================================
- 7. ADVANCED ANALYTICS
- ============================================================

- 7a. Profit margin analysis with percentile ranking
SELECT
    p.product_name,
    p.category,
    SUM(o.sales) AS total_sales,
    SUM(o.profit) AS total_profit,
    ROUND(SUM(o.profit) / NULLIF(SUM(o.sales), 0) * 100, 2) AS profit_margin_pct,
    PERCENT_RANK() OVER (ORDER BY SUM(o.profit) / NULLIF(SUM(o.sales), 0)) AS profit_percentile
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY p.product_name, p.category;
GO


- 7b. Discount impact analysis: compare profit with and without discounts
WITH discount_analysis AS (
    SELECT
        CASE
            WHEN discount = 0 THEN 'No Discount'
            WHEN discount <= 0.10 THEN 'Low (1-10%)'
            WHEN discount <= 0.20 THEN 'Medium (11-20%)'
            ELSE 'High (>20%)'
        END AS discount_band,
        COUNT(*) AS order_count,
        SUM(sales) AS total_sales,
        SUM(profit) AS total_profit,
        ROUND(AVG(profit), 2) AS avg_profit_per_order
    FROM Orders
    GROUP BY
        CASE
            WHEN discount = 0 THEN 'No Discount'
            WHEN discount <= 0.10 THEN 'Low (1-10%)'
            WHEN discount <= 0.20 THEN 'Medium (11-20%)'
            ELSE 'High (>20%)'
        END
)
SELECT
    discount_band,
    order_count,
    total_sales,
    total_profit,
    avg_profit_per_order,
    ROUND(total_profit * 100.0 / NULLIF(total_sales, 0), 2) AS margin_pct
FROM discount_analysis
ORDER BY margin_pct DESC;
GO


- 7c. State-level performance with city-level drill-down
WITH state_summary AS (
    SELECT
        c.state,
        c.city,
        SUM(o.sales) AS total_sales,
        SUM(o.profit) AS total_profit,
        COUNT(DISTINCT o.order_id) AS order_count,
        SUM(SUM(o.sales)) OVER (PARTITION BY c.state) AS state_total_sales,
        RANK() OVER (
            PARTITION BY c.state
            ORDER BY SUM(o.sales) DESC
        ) AS city_rank_in_state
    FROM Orders o
    JOIN Customers c ON o.customer_id = c.customer_id
    GROUP BY c.state, c.city
)
SELECT
    state,
    city,
    total_sales,
    total_profit,
    order_count,
    ROUND(total_sales * 100.0 / NULLIF(state_total_sales, 0), 2) AS pct_of_state_sales,
    city_rank_in_state
FROM state_summary
ORDER BY state, city_rank_in_state;
GO