sales_analytics.sql

-- Project 3: Sales Analytics & Query Optimization

-- 1️ Create Database

CREATE DATABASE sales;
GO
USE sales;
GO

-- 2️ Create Tables
CREATE TABLE customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100),
    email NVARCHAR(100) UNIQUE,
    region NVARCHAR(50),
    created_at DATE
);

CREATE TABLE products (
    product_id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100),
    category NVARCHAR(50),
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    order_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status NVARCHAR(20) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT IDENTITY(1,1) PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    discount_rate DECIMAL(5,2) DEFAULT 0.0,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 3️ Insert Sample Data
--   4: SEED DATA
--   10 customers, 12 products, 32 orders across 14 months
--   Seasonal spikes in Nov/Dec, discounts, one cancelled order
--   Revenue = quantity * unit_price * (1 - discount_rate)
--   Completed = status IN ('shipped','delivered')
 
-- Customers
INSERT INTO customers (name, email, created_at, region) VALUES
  ('Alice Martin',  'alice@example.com',  '2023-01-10', 'North'),
  ('Bob Hassan',    'bob@example.com',    '2023-02-15', 'South'),
  ('Carol Nour',    'carol@example.com',  '2023-03-20', 'East'),
  ('David Kim',     'david@example.com',  '2023-04-05', 'West'),
  ('Eva Petrov',    'eva@example.com',    '2023-05-18', 'North'),
  ('Frank Diaz',    'frank@example.com',  '2023-06-22', 'South'),
  ('Grace Liu',     'grace@example.com',  '2023-07-30', 'East'),
  ('Hana Yilmaz',   'hana@example.com',   '2023-08-14', 'West'),
  ('Ivan Osei',     'ivan@example.com',   '2023-09-09', 'North'),
  ('Julia Brown',   'julia@example.com',  '2023-10-01', 'South');
 select * from customers


-- Products
INSERT INTO products (name, category, price) VALUES
  ('Laptop Pro 15',      'Electronics',  1200.00),
  ('Wireless Mouse',     'Electronics',    25.00),
  ('USB-C Hub',          'Electronics',    45.00),
  ('Office Chair',       'Furniture',     320.00),
  ('Standing Desk',      'Furniture',     550.00),
  ('Notebook A5',        'Stationery',      5.00),
  ('Ballpoint Pens x10', 'Stationery',      8.00),
  ('Python Book',        'Books',          40.00),
  ('SQL Mastery',        'Books',          35.00),
  ('Headphones BT',      'Electronics',   150.00),
  ('Monitor 27"',        'Electronics',   400.00),
  ('Ergonomic Keyboard', 'Electronics',    90.00);

-- Orders
INSERT INTO orders (customer_id, order_date, status) VALUES
  (1,  '2024-01-05', 'delivered'),   -- 1
  (2,  '2024-01-18', 'shipped'),     -- 2
  (3,  '2024-02-02', 'delivered'),   -- 3
  (4,  '2024-02-20', 'delivered'),   -- 4
  (5,  '2024-03-08', 'shipped'),     -- 5
  (6,  '2024-03-25', 'cancelled'),   -- 6  cancelled order
  (7,  '2024-04-10', 'delivered'),   -- 7
  (8,  '2024-04-28', 'delivered'),   -- 8
  (1,  '2024-05-03', 'shipped'),     -- 9
  (9,  '2024-05-19', 'delivered'),   -- 10
  (2,  '2024-06-07', 'delivered'),   -- 11
  (10, '2024-06-22', 'shipped'),     -- 12
  (3,  '2024-07-14', 'delivered'),   -- 13
  (4,  '2024-07-29', 'shipped'),     -- 14
  (5,  '2024-08-05', 'delivered'),   -- 15
  (6,  '2024-08-20', 'delivered'),   -- 16
  (7,  '2024-09-11', 'shipped'),     -- 17
  (8,  '2024-09-30', 'delivered'),   -- 18
  (9,  '2024-10-06', 'delivered'),   -- 19
  (10, '2024-10-21', 'shipped'),     -- 20
  (1,  '2024-11-01', 'delivered'),   -- 21  (November spike)
  (2,  '2024-11-05', 'delivered'),   -- 22
  (3,  '2024-11-10', 'shipped'),     -- 23
  (4,  '2024-11-15', 'delivered'),   -- 24
  (5,  '2024-11-22', 'delivered'),   -- 25
  (6,  '2024-12-01', 'delivered'),   -- 26  (December spike)
  (7,  '2024-12-05', 'shipped'),     -- 27
  (8,  '2024-12-10', 'delivered'),   -- 28
  (9,  '2024-12-18', 'delivered'),   -- 29
  (10, '2024-12-26', 'shipped'),     -- 30
  (1,  '2025-01-08', 'delivered'),   -- 31  (14th month)
  (2,  '2025-01-20', 'shipped');     -- 32

-- Order Items
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_rate) VALUES
  (1,  1, 1, 1200.00, 0.000),
  (1,  2, 1,   25.00, 0.000),
  (2,  3, 2,   45.00, 0.050),
  (3,  4, 1,  320.00, 0.100),
  (4,  5, 1,  550.00, 0.000),
  (5,  6, 5,    5.00, 0.000),
  (5,  7, 2,    8.00, 0.000),
  (6,  8, 1,   40.00, 0.000),
  (7,  9, 1,   35.00, 0.000),
  (7, 10, 1,  150.00, 0.000),
  (8, 11, 1,  400.00, 0.050),
  (9, 12, 1,   90.00, 0.000),
  (10, 1, 1, 1200.00, 0.150),
  (11, 2, 3,   25.00, 0.000),
  (11, 3, 1,   45.00, 0.000),
  (12, 4, 1,  320.00, 0.000),
  (13, 5, 1,  550.00, 0.100),
  (14, 6,10,    5.00, 0.000),
  (14, 7, 5,    8.00, 0.000),
  (15, 8, 2,   40.00, 0.050),
  (16, 9, 1,   35.00, 0.000),
  (16,10, 1,  150.00, 0.100),
  (17,11, 2,  400.00, 0.000),
  (18,12, 1,   90.00, 0.050),
  (19, 1, 1, 1200.00, 0.200),
  (20, 2, 4,   25.00, 0.000),
  (21, 1, 1, 1200.00, 0.100),
  (21,11, 1,  400.00, 0.100),
  (22, 5, 1,  550.00, 0.000),
  (22,12, 2,   90.00, 0.050),
  (23,10, 2,  150.00, 0.000),
  (23, 4, 1,  320.00, 0.050),
  (24, 9, 2,   35.00, 0.000),
  (24, 8, 3,   40.00, 0.050),
  (25, 1, 1, 1200.00, 0.000),
  (25, 3, 3,   45.00, 0.000),
  (26, 1, 2, 1200.00, 0.150),
  (26,10, 1,  150.00, 0.000),
  (27, 5, 1,  550.00, 0.050),
  (27,11, 1,  400.00, 0.100),
  (28, 4, 2,  320.00, 0.000),
  (28,12, 1,   90.00, 0.000),
  (29, 1, 1, 1200.00, 0.100),
  (29, 2, 5,   25.00, 0.000),
  (30, 9, 3,   35.00, 0.000),
  (30, 8, 4,   40.00, 0.050),
  (31, 1, 1, 1200.00, 0.050),
  (32, 3, 2,   45.00, 0.000);
GO
select * from customers
select * from products
select * from order_items
select * from orders

--Indexes for Optimization
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_orders_status_date ON orders(status, order_date);

--4 Views
--  Monthly Revenue
CREATE VIEW vw_monthly_revenue AS
SELECT 
    DATEFROMPARTS(YEAR(o.order_date), MONTH(o.order_date), 1) AS month_start,
    SUM(oi.quantity * oi.unit_price * (1 - oi.discount_rate)) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status IN ('shipped', 'delivered')
GROUP BY DATEFROMPARTS(YEAR(o.order_date), MONTH(o.order_date), 1);



--  Active Customers (90 days)
CREATE VIEW vw_active_customers_90d AS
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status IN ('shipped', 'delivered')
AND o.order_date >= DATEADD(DAY, -90, GETDATE());

SELECT * FROM vw_active_customers_90d 
-- مطلعتش نتيجة عشان الداتا اخرها 2025 واحنا في 2026

CREATE VIEW vw_active_customers AS
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status IN ('shipped', 'delivered')
AND o.order_date >= DATEADD(DAY, -90, (SELECT MAX(order_date) FROM orders));

SELECT * FROM vw_active_customers

--  Top Selling Products
CREATE VIEW vw_top_selling_products AS
SELECT 
    p.name,
    SUM(oi.quantity) AS total_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status IN ('shipped', 'delivered')
GROUP BY p.name;

SELECT * FROM vw_top_selling_products

-- 5 High Value Customers (CTE)
WITH customer_spending AS (
    SELECT 
        o.customer_id,
        SUM(oi.quantity * oi.unit_price * (1 - oi.discount_rate)) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status IN ('shipped', 'delivered')
    GROUP BY o.customer_id
)
SELECT *
FROM customer_spending
WHERE total_spent > (SELECT AVG(total_spent) FROM customer_spending);

--  6  Reports
--  Monthly Revenue with Growth
SELECT 
    month_start,
    revenue,
    LAG(revenue) OVER (ORDER BY month_start) AS prev_month_revenue,
    CASE 
        WHEN LAG(revenue) OVER (ORDER BY month_start) IS NULL THEN NULL
        ELSE 
            (revenue - LAG(revenue) OVER (ORDER BY month_start)) * 100.0 
            / LAG(revenue) OVER (ORDER BY month_start)
    END AS mom_growth_pct
FROM vw_monthly_revenue;

--  Category Performance (Last 12 Months)
SELECT 
    p.category,
    COUNT(DISTINCT o.order_id) AS orders,
    SUM(oi.quantity) AS items,
    SUM(oi.quantity * oi.unit_price * (1 - oi.discount_rate)) AS revenue,
    SUM(oi.quantity * oi.unit_price * (1 - oi.discount_rate)) / COUNT(DISTINCT o.order_id) AS avg_order_value
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status IN ('shipped', 'delivered')
AND o.order_date >= DATEADD(MONTH, -12, (SELECT MAX(order_date) FROM orders))
GROUP BY p.category;

--  Regional Leaderboard
SELECT 
    c.region,
    COUNT(DISTINCT c.customer_id) AS customers,
    SUM(oi.quantity * oi.unit_price * (1 - oi.discount_rate)) AS revenue,
    (
        SELECT TOP 1 p.name
        FROM products p
        JOIN order_items oi2 ON p.product_id = oi2.product_id
        JOIN orders o2 ON o2.order_id = oi2.order_id
        JOIN customers c2 ON c2.customer_id = o2.customer_id
        WHERE o2.status IN ('shipped', 'delivered')
        AND c2.region = c.region
        GROUP BY p.name
        ORDER BY SUM(oi2.quantity * oi2.unit_price * (1 - oi2.discount_rate)) DESC
    ) AS top_product
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status IN ('shipped', 'delivered')
GROUP BY c.region;