sqlite_queries.sql

- SQLite / PostgreSQL Compatible Sales Analytics Queries
- =======================================================
- Standard ANSI SQL queries demonstrating Window Functions, CTEs,
- running totals, moving averages, and cross-tab aggregations.

- 1. Schema Creation
CREATE TABLE IF NOT EXISTS customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL,
    segment TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT NOT NULL,
    sub_category TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS orders (
    order_id INTEGER PRIMARY KEY,
    order_date TEXT NOT NULL,
    customer_id INTEGER,
    product_id INTEGER,
    sales REAL NOT NULL,
    quantity INTEGER NOT NULL,
    discount REAL NOT NULL,
    profit REAL NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

- 2. Window Functions: Product Sales Ranking
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 sales_rank,
    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;

- 3. Category Rank (Partitioned)
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;

- 4. Customer Spending Quartiles (NTILE)
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;

- 5. Month-over-Month Sales Growth (LAG)
WITH monthly_sales AS (
    SELECT
        STRFTIME('%Y-%m', order_date) AS sales_month,
        SUM(sales) AS monthly_sales
    FROM orders
    GROUP BY STRFTIME('%Y-%m', order_date)
)
SELECT
    sales_month,
    monthly_sales,
    LAG(monthly_sales, 1) OVER (ORDER BY sales_month) AS prev_month_sales,
    ROUND(
        (monthly_sales - LAG(monthly_sales, 1) OVER (ORDER BY sales_month))
        / LAG(monthly_sales, 1) OVER (ORDER BY sales_month) * 100.0,
        2
    ) AS mom_growth_pct
FROM monthly_sales;

- 6. Running Totals (Cumulative Sum)
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
FROM daily_sales;

- 7. Conditional Aggregation (Cross-Tab / Pivot Alternative)
SELECT
    STRFTIME('%m', o.order_date) AS order_month,
    SUM(CASE WHEN p.category = 'Technology' THEN o.sales ELSE 0 END) AS technology_sales,
    SUM(CASE WHEN p.category = 'Furniture' THEN o.sales ELSE 0 END) AS furniture_sales,
    SUM(CASE WHEN p.category = 'Office Supplies' THEN o.sales ELSE 0 END) AS office_supplies_sales
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY STRFTIME('%m', o.order_date)
ORDER BY order_month;