03_analysis_queries.sql

-- Customer & Order Analysis Queries
-- This file demonstrates SQL concepts: Joins, CTEs, Window Functions, and Aggregations

-- ============================================================================
-- JOINS DEMONSTRATION
-- ============================================================================

-- 1. INNER JOIN: Get all orders with customer details
-- Purpose: Combine order and customer data to see who placed each order
-- Returns: Only orders that have matching customer records (excludes orphaned orders)
SELECT 
    o.order_id,              -- Unique order identifier
    o.order_date,            -- When the order was placed
    c.first_name,            -- Customer's first name
    c.last_name,             -- Customer's last name
    c.email,                 -- Customer's email address
    o.status                 -- Order status (e.g., 'completed', 'pending')
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id  -- Match orders to customers
ORDER BY o.order_date DESC;  -- Show most recent orders first

-- 2. LEFT JOIN: Get all customers and their order counts (including customers with no orders)
-- Purpose: Identify all customers, even those who haven't placed any orders yet
-- Returns: All customers with their order count (0 for customers with no orders)
-- Business Use: Find inactive customers for re-engagement campaigns
SELECT 
    c.customer_id,          -- Customer identifier
    c.first_name,            -- Customer's first name
    c.last_name,             -- Customer's last name
    COUNT(o.order_id) AS total_orders  -- Count of orders (0 if no orders)
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id  -- Include all customers, even without orders
GROUP BY c.customer_id, c.first_name, c.last_name   -- Group by customer to aggregate orders
ORDER BY total_orders DESC;  -- Show most active customers first

-- 3. MULTIPLE JOINS: Get detailed order information with customer and product details
SELECT 
    o.order_id,
    o.order_date,
    c.first_name || ' ' || c.last_name AS customer_name,
    p.product_name,
    p.category,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_date DESC, o.order_id;

-- ============================================================================
-- CTEs (COMMON TABLE EXPRESSIONS) DEMONSTRATION
-- ============================================================================

-- 4. Simple CTE: Calculate order totals
-- Purpose: Use CTE to break down complex query into readable steps
-- Step 1: Calculate total value for each order
-- Step 2: Join with customer data to show who placed each order
-- Benefit: CTE makes the query more readable and maintainable
WITH order_totals AS (
    -- First, calculate the total value of each order by summing line items
    SELECT 
        o.order_id,                                    -- Order identifier
        o.customer_id,                                 -- Customer who placed order
        o.order_date,                                  -- Order date
        SUM(oi.quantity * oi.unit_price) AS order_total  -- Total: quantity × price for all items
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.order_id, o.customer_id, o.order_date  -- Group to aggregate line items
)
-- Now use the CTE to get customer names and order totals
SELECT 
    ot.order_id,
    ot.order_date,
    c.first_name || ' ' || c.last_name AS customer_name,  -- Concatenate first and last name
    ot.order_total
FROM order_totals ot
INNER JOIN customers c ON ot.customer_id = c.customer_id
ORDER BY ot.order_total DESC;  -- Show highest value orders first

-- 5. Multiple CTEs: Calculate customer lifetime value and average order value
WITH customer_order_totals AS (
    SELECT 
        o.customer_id,
        o.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id, o.order_id
),
customer_summary AS (
    SELECT 
        customer_id,
        COUNT(DISTINCT order_id) AS order_count,
        SUM(order_total) AS lifetime_value,
        AVG(order_total) AS avg_order_value
    FROM customer_order_totals
    GROUP BY customer_id
)
SELECT 
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    cs.order_count,
    ROUND(cs.lifetime_value, 2) AS lifetime_value,
    ROUND(cs.avg_order_value, 2) AS avg_order_value
FROM customer_summary cs
INNER JOIN customers c ON cs.customer_id = c.customer_id
ORDER BY cs.lifetime_value DESC;

-- 6. CTE with Window Functions: Calculate running totals
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(oi.quantity * oi.unit_price) AS monthly_total
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT 
    month,
    monthly_total,
    SUM(monthly_total) OVER (ORDER BY month) AS running_total
FROM monthly_revenue
ORDER BY month;

-- ============================================================================
-- WINDOW FUNCTIONS DEMONSTRATION
-- ============================================================================

-- 7. ROW_NUMBER: Rank orders by value within each customer
-- Purpose: Identify each customer's highest and lowest value orders
-- Window Function: ROW_NUMBER() assigns unique sequential numbers within each partition
-- Partition: Groups by customer_id, so ranking resets for each customer
-- Business Use: Find customers' best purchase to understand their preferences
SELECT 
    o.order_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    -- Window function: Rank orders within each customer's order history
    -- PARTITION BY: Separate ranking for each customer
    -- ORDER BY: Rank by order value (highest first)
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS order_rank
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, c.first_name, c.last_name, o.order_date, o.customer_id
ORDER BY c.last_name, order_rank;  -- Sort by customer, then by their order rank

-- 8. RANK and DENSE_RANK: Rank products by total sales
SELECT 
    p.product_name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS total_sales,
    RANK() OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS sales_rank,
    DENSE_RANK() OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS sales_dense_rank
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY sales_rank;

-- 9. LAG and LEAD: Compare monthly revenue with previous and next month
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', o.order_date) AS month,
        SUM(oi.quantity * oi.unit_price) AS monthly_total
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT 
    month,
    monthly_total,
    LAG(monthly_total) OVER (ORDER BY month) AS previous_month,
    LEAD(monthly_total) OVER (ORDER BY month) AS next_month,
    monthly_total - LAG(monthly_total) OVER (ORDER BY month) AS month_over_month_change
FROM monthly_revenue
ORDER BY month;

-- 10. PARTITION BY: Calculate customer's order total vs their average order total
SELECT 
    o.order_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    o.order_date,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    AVG(SUM(oi.quantity * oi.unit_price)) OVER (PARTITION BY o.customer_id) AS customer_avg_order,
    SUM(oi.quantity * oi.unit_price) - AVG(SUM(oi.quantity * oi.unit_price)) OVER (PARTITION BY o.customer_id) AS difference_from_avg
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, c.first_name, c.last_name, o.order_date, o.customer_id
ORDER BY c.last_name, o.order_date;

-- 11. PERCENT_RANK and CUME_DIST: Analyze order value distribution
WITH order_values AS (
    SELECT 
        o.order_id,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.order_id
)
SELECT 
    order_id,
    order_total,
    PERCENT_RANK() OVER (ORDER BY order_total) AS percent_rank,
    CUME_DIST() OVER (ORDER BY order_total) AS cumulative_distribution
FROM order_values
ORDER BY order_total DESC;

-- ============================================================================
-- AGGREGATIONS DEMONSTRATION
-- ============================================================================

-- 12. Basic Aggregations: Product sales summary
SELECT 
    p.product_name,
    p.category,
    COUNT(DISTINCT oi.order_id) AS times_ordered,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    AVG(oi.unit_price) AS avg_unit_price,
    MIN(oi.unit_price) AS min_price,
    MAX(oi.unit_price) AS max_price
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC;

-- 13. GROUP BY with HAVING: Products with high sales volume
SELECT 
    p.category,
    COUNT(DISTINCT oi.product_id) AS products_sold,
    SUM(oi.quantity) AS total_units_sold,
    SUM(oi.quantity * oi.unit_price) AS category_revenue
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category
HAVING SUM(oi.quantity * oi.unit_price) > 1000
ORDER BY category_revenue DESC;

-- 14. Multiple Aggregations: Customer purchase behavior
SELECT 
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COUNT(DISTINCT oi.product_id) AS unique_products_purchased,
    SUM(oi.quantity) AS total_items_purchased,
    SUM(oi.quantity * oi.unit_price) AS total_spent,
    AVG(oi.quantity * oi.unit_price) AS avg_item_value
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

-- 15. Conditional Aggregations: Revenue by product category
SELECT 
    p.category,
    COUNT(DISTINCT oi.order_id) AS order_count,
    SUM(CASE WHEN p.category = 'Electronics' THEN oi.quantity * oi.unit_price ELSE 0 END) AS electronics_revenue,
    SUM(CASE WHEN p.category = 'Furniture' THEN oi.quantity * oi.unit_price ELSE 0 END) AS furniture_revenue,
    SUM(CASE WHEN p.category = 'Appliances' THEN oi.quantity * oi.unit_price ELSE 0 END) AS appliances_revenue,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY total_revenue DESC;