Quick Start

psql -U postgres

CREATE DATABASE ecommerce;

\c ecommerce

-- Users table with authentication
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample users
INSERT INTO users (username, email, password_hash)
VALUES
    ('alice', 'alice@example.com', '$2a$10$...'),
    ('bob', 'bob@example.com', '$2a$10$...'),
    ('charlie', 'charlie@example.com', '$2a$10$...');

-- Verify insertion
SELECT id, username, email FROM users;

-- Products table with categories
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    category VARCHAR(50) NOT NULL,
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample products
INSERT INTO products (name, description, price, category, stock_quantity)
VALUES
    ('Laptop', 'High-performance laptop', 999.99, 'Electronics', 50),
    ('Mouse', 'Wireless optical mouse', 19.99, 'Electronics', 200),
    ('Desk Chair', 'Ergonomic office chair', 299.99, 'Furniture', 30),
    ('Coffee Mug', 'Ceramic coffee mug', 9.99, 'Kitchen', 100);

-- Verify insertion
SELECT id, name, price, category, stock_quantity FROM products;

-- Orders table linking to users
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    total_amount NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample orders
INSERT INTO orders (user_id, status, total_amount)
VALUES
    (1, 'delivered', 1019.98),  -- Alice
    (2, 'processing', 299.99),  -- Bob
    (1, 'pending', 9.99);       -- Alice

-- Verify insertion
SELECT id, user_id, status, total_amount FROM orders;

-- Order items (many-to-many: orders ↔ products)
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample order items
INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase)
VALUES
    (1, 1, 1, 999.99),  -- Order 1: 1 Laptop
    (1, 2, 1, 19.99),   -- Order 1: 1 Mouse
    (2, 3, 1, 299.99),  -- Order 2: 1 Desk Chair
    (3, 4, 1, 9.99);    -- Order 3: 1 Coffee Mug

-- Verify insertion
SELECT id, order_id, product_id, quantity, price_at_purchase FROM order_items;

-- Get orders with user information
SELECT
    o.id AS order_id,
    u.username,
    u.email,
    o.status,
    o.total_amount,
    o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id
ORDER BY o.created_at DESC;

-- Get all products with total quantity sold (including products with no sales)
SELECT
    p.id,
    p.name,
    p.category,
    p.price,
    COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category, p.price
ORDER BY total_sold DESC;

-- Get complete order details (order, user, items, products)
SELECT
    o.id AS order_id,
    u.username,
    o.status,
    p.name AS product_name,
    oi.quantity,
    oi.price_at_purchase,
    (oi.quantity * oi.price_at_purchase) AS item_total
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
ORDER BY o.id, oi.id;

-- Count orders grouped by status
SELECT
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

-- Calculate total revenue by product category
SELECT
    p.category,
    COUNT(DISTINCT oi.order_id) AS num_orders,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.price_at_purchase) AS total_revenue
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.category
ORDER BY total_revenue DESC;

-- Find users with more than 1 order
SELECT
    u.username,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
HAVING COUNT(o.id) > 1
ORDER BY total_spent DESC;

-- Create index on products.category for faster filtering
CREATE INDEX idx_products_category ON products(category);

-- Create index on orders.user_id for faster joins
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Create index on order_items.order_id for faster joins
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Verify indexes
\di

-- Create composite index for common query pattern
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- This index speeds up queries filtering by both user_id and status
SELECT * FROM orders WHERE user_id = 1 AND status = 'delivered';

-- Analyze query execution plan
EXPLAIN ANALYZE
SELECT p.name, p.price
FROM products p
WHERE p.category = 'Electronics';

-- Start transaction
BEGIN;

-- Deduct product stock
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 1;

-- Create order
INSERT INTO orders (user_id, status, total_amount)
VALUES (3, 'pending', 999.99);

-- Create order item
INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase)
VALUES (currval('orders_id_seq'), 1, 1, 999.99);

-- Commit transaction (make changes permanent)
COMMIT;

-- Verify changes
SELECT stock_quantity FROM products WHERE id = 1;

-- Start transaction
BEGIN;

-- Try to create order with negative stock
UPDATE products
SET stock_quantity = stock_quantity - 100
WHERE id = 2;

-- Check constraint violation (stock would be negative)
SELECT stock_quantity FROM products WHERE id = 2;

-- Rollback transaction (undo changes)
ROLLBACK;

-- Verify rollback - stock unchanged
SELECT stock_quantity FROM products WHERE id = 2;

-- Add metadata column to products
ALTER TABLE products ADD COLUMN metadata JSONB;

-- Update products with JSON metadata
UPDATE products
SET metadata = '{"brand": "TechCorp", "warranty_years": 2, "features": ["SSD", "16GB RAM", "Backlit Keyboard"]}'
WHERE id = 1;

UPDATE products
SET metadata = '{"brand": "TechCorp", "wireless": true, "dpi": 1600}'
WHERE id = 2;

UPDATE products
SET metadata = '{"brand": "ComfortSeating", "adjustable": true, "material": "mesh"}'
WHERE id = 3;

UPDATE products
SET metadata = '{"brand": "KitchenPro", "capacity_ml": 350, "dishwasher_safe": true}'
WHERE id = 4;

-- Verify JSON data
SELECT id, name, metadata FROM products;

-- Query products by JSON field
SELECT name, metadata->>'brand' AS brand
FROM products
WHERE metadata->>'brand' = 'TechCorp';

-- Extract JSON field as text (->>')
SELECT name, metadata->>'brand' AS brand FROM products WHERE id = 1;
-- Output: Laptop | TechCorp

-- Extract JSON field as JSON (->)
SELECT name, metadata->'features' AS features FROM products WHERE id = 1;
-- Output: Laptop | ["SSD", "16GB RAM", "Backlit Keyboard"]

-- Check JSON key existence (?)
SELECT name FROM products WHERE metadata ? 'wireless';
-- Output: Mouse

-- Check JSON value (@>)
SELECT name FROM products WHERE metadata @> '{"wireless": true}';
-- Output: Mouse

-- Extract nested JSON path (#>>)
SELECT name, metadata#>>'{features,0}' AS first_feature FROM products WHERE id = 1;
-- Output: Laptop | SSD

-- Create GIN index for JSON containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- Fast query using index
SELECT name FROM products WHERE metadata @> '{"brand": "TechCorp"}';

-- Add tsvector column for full-text search
ALTER TABLE products ADD COLUMN search_vector tsvector;

-- Populate search vector from name and description
UPDATE products
SET search_vector = to_tsvector('english', name || ' ' || COALESCE(description, ''));

-- Create GIN index for fast full-text search
CREATE INDEX idx_products_search ON products USING GIN (search_vector);

-- Verify search vectors
SELECT id, name, search_vector FROM products;

-- Search for products containing "laptop"
SELECT name, description
FROM products
WHERE search_vector @@ to_tsquery('english', 'laptop');

-- Search with relevance ranking
SELECT
    name,
    description,
    ts_rank(search_vector, query) AS rank
FROM products, to_tsquery('english', 'wireless | laptop') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Calculate order totals using CTE
WITH order_totals AS (
    SELECT
        order_id,
        SUM(quantity * price_at_purchase) AS calculated_total
    FROM order_items
    GROUP BY order_id
)
SELECT
    o.id,
    u.username,
    o.total_amount AS stored_total,
    ot.calculated_total,
    (o.total_amount = ot.calculated_total) AS totals_match
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_totals ot ON o.id = ot.order_id;

-- Find top customers and their favorite categories
WITH customer_spending AS (
    SELECT
        u.id AS user_id,
        u.username,
        SUM(o.total_amount) AS total_spent
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.username
),
customer_categories AS (
    SELECT
        u.id AS user_id,
        p.category,
        COUNT(*) AS purchase_count
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id
    GROUP BY u.id, p.category
)
SELECT
    cs.username,
    cs.total_spent,
    cc.category AS favorite_category,
    cc.purchase_count
FROM customer_spending cs
INNER JOIN customer_categories cc ON cs.user_id = cc.user_id
ORDER BY cs.total_spent DESC, cc.purchase_count DESC;