Relational Databases (SQL) - Fundamentals

-- Example: User and UserProfile
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    profile_id SERIAL PRIMARY KEY,
    user_id INTEGER UNIQUE REFERENCES users(user_id),
    bio TEXT,
    avatar_url VARCHAR(255)
);

-- Example: Department and Employees
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(department_id)
);

-- Example: Students and Courses
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Junction table
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);

-- Create a complete e-commerce database schema

-- Categories table
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_category_id INTEGER REFERENCES categories(category_id),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    category_id INTEGER REFERENCES categories(category_id),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
    sku VARCHAR(50) UNIQUE,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Addresses table (one customer can have multiple addresses)
CREATE TABLE addresses (
    address_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id) ON DELETE CASCADE,
    address_type VARCHAR(20) CHECK (address_type IN ('billing', 'shipping')),
    street_address VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(100) NOT NULL,
    is_default BOOLEAN DEFAULT false
);

-- Orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    shipping_address_id INTEGER REFERENCES addresses(address_id),
    billing_address_id INTEGER REFERENCES addresses(address_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending' 
        CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    subtotal DECIMAL(10, 2),
    tax DECIMAL(10, 2),
    shipping_cost DECIMAL(10, 2),
    total DECIMAL(10, 2),
    notes TEXT
);

-- Order items (junction table for orders and products)
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- Create indexes for common queries
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(is_active) WHERE is_active = true;
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_date ON orders(order_date);

-- Get all orders with customer and product details
SELECT 
    o.order_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    c.email,
    o.order_date,
    o.status,
    o.total,
    json_agg(
        json_build_object(
            'product', p.name,
            'quantity', oi.quantity,
            'price', oi.unit_price
        )
    ) AS items
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.order_id, c.first_name, c.last_name, c.email
ORDER BY o.order_date DESC;

-- Find top customers by total spending
SELECT 
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(o.total) AS total_spent,
    AVG(o.total) AS average_order_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status != 'cancelled'
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING SUM(o.total) > 100
ORDER BY total_spent DESC
LIMIT 10;

-- Products with low stock that have been ordered recently
SELECT 
    p.product_id,
    p.name,
    p.stock_quantity,
    COALESCE(SUM(oi.quantity), 0) AS units_sold_last_30_days
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id 
    AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
    AND o.status != 'cancelled'
WHERE p.stock_quantity < 10
    AND p.is_active = true
GROUP BY p.product_id, p.name, p.stock_quantity
ORDER BY p.stock_quantity ASC;

-- Running total and ranking
SELECT 
    order_id,
    customer_id,
    order_date,
    total,
    SUM(total) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS running_total,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS order_number,
    RANK() OVER (
        ORDER BY total DESC
    ) AS spending_rank
FROM orders
WHERE status != 'cancelled';

-- Month-over-month sales comparison
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS total_sales
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    month,
    total_sales,
    LAG(total_sales) OVER (ORDER BY month) AS previous_month,
    total_sales - LAG(total_sales) OVER (ORDER BY month) AS change,
    ROUND(
        (total_sales - LAG(total_sales) OVER (ORDER BY month)) * 100.0 / 
        NULLIF(LAG(total_sales) OVER (ORDER BY month), 0), 
        2
    ) AS percent_change
FROM monthly_sales
ORDER BY month;

-- Complex reporting query with CTEs
WITH customer_stats AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(total) AS total_spent,
        MIN(order_date) AS first_order,
        MAX(order_date) AS last_order
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
),
customer_segments AS (
    SELECT 
        customer_id,
        order_count,
        total_spent,
        first_order,
        last_order,
        CASE 
            WHEN total_spent >= 1000 AND order_count >= 5 THEN 'VIP'
            WHEN total_spent >= 500 OR order_count >= 3 THEN 'Regular'
            ELSE 'New'
        END AS segment
    FROM customer_stats
)
SELECT 
    c.customer_id,
    c.first_name || ' ' || c.last_name AS name,
    cs.segment,
    cs.order_count,
    cs.total_spent,
    cs.first_order,
    cs.last_order,
    CURRENT_DATE - cs.last_order::date AS days_since_last_order
FROM customers c
JOIN customer_segments cs ON c.customer_id = cs.customer_id
ORDER BY cs.total_spent DESC;

-- Create your tables here
-- Consider:
-- 1. What entities do you need?
-- 2. What are the relationships?
-- 3. What constraints should you add?

-- Users table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    bio TEXT,
    avatar_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts table
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tags table
CREATE TABLE tags (
    tag_id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

-- Post-Tags junction table (many-to-many)
CREATE TABLE post_tags (
    post_id INTEGER REFERENCES posts(post_id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(tag_id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- Comments table (self-referencing for replies)
CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(post_id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    parent_comment_id INTEGER REFERENCES comments(comment_id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Follows table (self-referencing many-to-many)
CREATE TABLE follows (
    follower_id INTEGER REFERENCES users(user_id) ON DELETE CASCADE,
    following_id INTEGER REFERENCES users(user_id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id != following_id)  -- Can't follow yourself
);

-- Likes table
CREATE TABLE post_likes (
    user_id INTEGER REFERENCES users(user_id) ON DELETE CASCADE,
    post_id INTEGER REFERENCES posts(post_id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, post_id)
);

-- Indexes
CREATE INDEX idx_posts_user ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published ON posts(published_at) WHERE status = 'published';
CREATE INDEX idx_comments_post ON comments(post_id);
CREATE INDEX idx_comments_user ON comments(user_id);

-- 1. Top 5 most followed users
SELECT 
    u.user_id,
    u.username,
    u.display_name,
    COUNT(f.follower_id) AS follower_count
FROM users u
LEFT JOIN follows f ON u.user_id = f.following_id
GROUP BY u.user_id, u.username, u.display_name
ORDER BY follower_count DESC
LIMIT 5;

-- 2. Posts with engagement metrics
SELECT 
    p.post_id,
    p.title,
    u.username AS author,
    p.published_at,
    COUNT(DISTINCT pl.user_id) AS like_count,
    COUNT(DISTINCT c.comment_id) AS comment_count
FROM posts p
JOIN users u ON p.user_id = u.user_id
LEFT JOIN post_likes pl ON p.post_id = pl.post_id
LEFT JOIN comments c ON p.post_id = c.post_id
WHERE p.status = 'published'
GROUP BY p.post_id, p.title, u.username, p.published_at
ORDER BY like_count DESC, comment_count DESC;

-- 3. User's feed (posts from followed users)
SELECT 
    p.post_id,
    p.title,
    p.excerpt,
    p.published_at,
    u.username AS author,
    u.avatar_url
FROM posts p
JOIN users u ON p.user_id = u.user_id
JOIN follows f ON p.user_id = f.following_id
WHERE f.follower_id = 1  -- Current user's ID
    AND p.status = 'published'
ORDER BY p.published_at DESC
LIMIT 20;

-- Slow query - find reasons and optimize
SELECT * FROM orders 
WHERE YEAR(order_date) = 2024 
    AND customer_id IN (
        SELECT customer_id FROM customers WHERE city = 'New York'
    );

-- Problems:
-- 1. SELECT * fetches unnecessary columns
-- 2. YEAR(order_date) prevents index usage
-- 3. Subquery might be inefficient

-- Optimized version
SELECT 
    o.order_id,
    o.customer_id,
    o.order_date,
    o.total,
    o.status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01' 
    AND o.order_date < '2025-01-01'
    AND c.city = 'New York';

-- Also ensure these indexes exist:
-- CREATE INDEX idx_orders_date ON orders(order_date);
-- CREATE INDEX idx_customers_city ON customers(city);